Check 8 bit in mysql during sorting
Hello, I need one help.To enable 8 bit language in bash we are exporting like this # For 8 bit Language support export LC_CTYPE=iso_8859_1 # For less to support 8 bit chars export LESSCHARSET=latin1 I am using local language for inserting values in mysql.While sorting in mysql is checking 7bits from the right.Is there any thing like this for checking 8 bits in mysql Thanks in advance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
www.fcliestal.ch
sql-problem! hello, please could you help me? after you made some work on your server.. the side of www.fcliestal.chdoes not work anymore because i did not change anything on it, i do not know, what to do... please could you reactivate the site? ... or at least give me some information what i have to do thank you very much, for your answer...! greetings from switzerland marcello zandona - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
syntax Q
Hello... I just encountered the following: let $a be a numeric php expression, a the corresponding integer field in a table. When I say: UPDATE table SET a=$a it denies the update. When I say: Update table SET a='$a' it performs the update. Uhm, now I am wondering. Isn't 'something' supposed to be a string? Cu, Andreas Sql, mysql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldump bug ?
Hello, I'm using mysql-max 3.23.49 binary distribution. I use mysqldump utility to dump tables from my DB. for example: mysqldump --opt -u root -p EFDB users users.sql users.sql file contains: -- -- MySQL dump 8.21 -- -- Host: localhostDatabase: EFDB - -- Server version 3.23.49-max-log -- -- Table structure for table 'users' -- DROP TABLE IF EXISTS users; CREATE TABLE users ( user_id int(10) unsigned NOT NULL auto_increment, name varchar(50) NOT NULL default '', login varchar(20) NOT NULL default '', password varchar(16) NOT NULL default '', email varchar(50) NOT NULL default '', org varchar(128) NOT NULL default '', country char(3) NOT NULL default '', description varchar(128) NOT NULL default '', PRIMARY KEY (user_id) ) TYPE=InnoDB; /*!4 ALTER TABLE users DISABLE KEYS */; . . . INSERT follows. - Now take a look at the line saying: /*!4 ALTER TABLE users DISABLE KEYS */; This means that alter table query will be executed only if MySQL version is 4.00.00 or higher. When i try to use mysql utility to import this dumped file (mysql -u root -p EFDB users.sql) I get the NO SQL QUERY ERROR. This is probably caused by misplaced semicolon ; at the end of the line. In my case, alter table query is ignored because my MySQL version is 3.23.49. Then mysql utility tries to interpret ; as the end of empty SQL query. I think, this semicolon should be placed within the /* */ comments. What do you think? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Selecting from within date range
I have a database table containing log files that are written by different individuals almost every day. So at any given time I want to be able to go through a web interface and get a list over logs written in any given month. People writing in this log also work in different department, a field also included in the log. Timestamps for the log is stored as datetime on the format -MM-DD HH:MM:SS So I want to make the following selection: Show me all log entries for department X for the given month So how would I go about to do write this query in the easiest way, given that I provide the year and month I want to view before the query is made? select * from LOG_TABLE where DEPARTMENT='X' . . . ??? Thanks in advance - Torkil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: syntax Q
Hello Andreas, I just encountered the following: let $a be a numeric php expression, a the corresponding integer field in a table. When I say: UPDATE table SET a=$a it denies the update. When I say: Update table SET a='$a' it performs the update. Uhm, now I am wondering. Isn't 'something' supposed to be a string? Agreed. What does the SQL CREATE statement look like? What does PHP's assignment of value to $a (and relevant statements leading up to it) look like? Are you sure (the PC thinks) they are both integers? Please advise, =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
COUNT UNIQUE
Given a table with some non-unique column theCol, what is the most efficient way to count the number of different values in theCol? I guess you can use a temporary table as a subselect: CREATE TEMPORARY TABLE tmp SELECT UNIQUE theCol FROM theTable; SELECT COUNT(*) FROM tmp; DROP TABLE tmp; But this seem obscenely inefficient- it's a situation just screaming out for a summary function: SELECT COUNT_UNIQUE(theCol) FROM theTable; Does such a function exist in mySQL? Is one planned? Further, using the first method, what are the efficiency and robustness differences between using 'SELECT UNIQUE theCol' and 'SELECT theCol GROUP BY theCol'? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Selecting from within date range
Torkil, I have a database table containing log files that are written by different individuals almost every day. So at any given time I want to be able to go through a web interface and get a list over logs written in any given month. People writing in this log also work in different department, a field also included in the log. Timestamps for the log is stored as datetime on the format -MM-DD HH:MM:SS So I want to make the following selection: Show me all log entries for department X for the given month So how would I go about to do write this query in the easiest way, given that I provide the year and month I want to view before the query is made? select * from LOG_TABLE where DEPARTMENT='X' . . . ??? AND Timestamp-field = CONCAT ( given-year, -, given-month, -01 00:00:00 ) AND Timestamp-fieldCONCAT ( given-year, -, given-month + 1, -01 00:00:00 ) CONCAT() discussed in 6.3.2 String Functions Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
WG: syntax Q
-Ursprüngliche Nachricht- Von: Andreas Dau [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 4. März 2002 10:37 An: 'DL Neil' Betreff: AW: syntax Q -Ursprüngliche Nachricht- Von: DL Neil [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 4. März 2002 10:09 An: Andreas Dau; [EMAIL PROTECTED] Betreff: Re: syntax Q Hello Andreas, I just encountered the following: let $a be a numeric php expression, a the corresponding integer field in a table. When I say: UPDATE table SET a=$a it denies the update. When I say: Update table SET a='$a' it performs the update. Uhm, now I am wondering. Isn't 'something' supposed to be a string? Agreed. What does the SQL CREATE statement look like? Created with phpmyadmin, column type integer(10). Value to be inserted comes from a method=post script at no point treated other as a numeric... What does PHP's assignment of value to $a (and relevant statements leading up to it) look like? Are you sure (the PC thinks) they are both integers? Please advise, =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: COUNT UNIQUE
Rob wrote : Given a table with some non-unique column theCol, what is the most efficient way to count the number of different values in theCol? I guess you can use a temporary table as a subselect: CREATE TEMPORARY TABLE tmp SELECT UNIQUE theCol FROM theTable; SELECT COUNT(*) FROM tmp; DROP TABLE tmp; But this seem obscenely inefficient- it's a situation just screaming out for a summary function: SELECT COUNT_UNIQUE(theCol) FROM theTable; Does such a function exist in mySQL? Is one planned? Further, using the first method, what are the efficiency and robustness differences between using 'SELECT UNIQUE theCol' and 'SELECT theCol GROUP BY theCol'? Hi, SELECT COUNT(DISTINCT theCol) FROM theTable; should do it. Regards -- Joseph Bueno NetClub/Trader.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
mysqlbug
Hi , I downloaded mysql-3.22.32.tar.gz and began installing the source. I installed this on Solaris 2.6. However when I 'make', I came across this problem: -- Making all in support-files make: Fatal error: Don't know how to make target `all' Current working directory /net/mscsvr_f01/vol/vol0/home0/ckng/RDBMS/mysql-3.22.3 2/support-files *** Error code 1 make: Fatal error: Command failed for target `all-recursive' Current working directory /net/mscsvr_f01/vol/vol0/home0/ckng/RDBMS/mysql-3.22.3 2 *** Error code 1 make: Fatal error: Command failed for target `all-recursive-am' -- Could you please advise what needs to be done? Thank you. Regards, Edwin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: syntax Q
-Ursprüngliche Nachricht- Von: DL Neil [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 4. März 2002 11:03 An: Andreas Dau Betreff: Re: syntax Q Hello Andreas, I just encountered the following: let $a be a numeric php expression, a the corresponding integer field in a table. When I say: UPDATE table SET a=$a it denies the update. When I say: Update table SET a='$a' it performs the update. Uhm, now I am wondering. Isn't 'something' supposed to be a string? Agreed. What does the SQL CREATE statement look like? What does PHP's assignment of value to $a (and relevant statements leading up to it) look like? Are you sure (the PC thinks) they are both integers? Created with phpmyadmin, column type integer(10). Value to be inserted comes from a method=post script at no point treated other as a numeric... =uh-oh, my theory is looking shaky!? However such conclusion (numeric) may not be the case. Try running the 'form' and inserting a specific integer value, eg 7. Put a debug routine like this into the PHP after the POST is processed and before the MySQL call: echo Value of a appears to be $a~; if ( $a == 7 ) { echo PHP says it is seven; } else { echo PHP says it is not seven; } if ( $a === 7 ) { echo PHP says it is integer seven ; } else { echo PHP says it is not integer seven ; } echo BR; (code not syntax checked) Please advise, =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
AW: syntax Q
Thx for ur quick help I will check after a short nap *ggg* -Ursprüngliche Nachricht- Von: DL Neil [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 4. März 2002 11:03 An: Andreas Dau Betreff: Re: syntax Q Hello Andreas, I just encountered the following: let $a be a numeric php expression, a the corresponding integer field in a table. When I say: UPDATE table SET a=$a it denies the update. When I say: Update table SET a='$a' it performs the update. Uhm, now I am wondering. Isn't 'something' supposed to be a string? Agreed. What does the SQL CREATE statement look like? What does PHP's assignment of value to $a (and relevant statements leading up to it) look like? Are you sure (the PC thinks) they are both integers? Created with phpmyadmin, column type integer(10). Value to be inserted comes from a method=post script at no point treated other as a numeric... =uh-oh, my theory is looking shaky!? However such conclusion (numeric) may not be the case. Try running the 'form' and inserting a specific integer value, eg 7. Put a debug routine like this into the PHP after the POST is processed and before the MySQL call: echo Value of a appears to be $a~; if ( $a == 7 ) { echo PHP says it is seven; } else { echo PHP says it is not seven; } if ( $a === 7 ) { echo PHP says it is integer seven ; } else { echo PHP says it is not integer seven ; } echo BR; (code not syntax checked) Please advise, =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
Many processes in SHOW PROCESSLIST;
Hi, We use mysql 1.23.47 as a database for our PHP based accounting system. We use mysql_pconnect() to get a connection to the DB. This morning we couldn't log in because mysql sent back a 'too many open connection!' (or someting like that) error message. If I use the command SHOW PROCESSLIST than I can see many 'Sleep' processes. If I try to kill some processes by KILL processnumber than newer processes appear in the list. Is there any way to kill all of the hanging processes? And why are so many processes if we use mysql_pconnect() which uses the same connection what the PHP user opened before? Thanks in advance! Regards; Istvan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: COUNT UNIQUE
In recent versions, you can do select count(distinct theCol) ... (I'm actually running one at this moment!!) On Mon, 2002-03-04 at 09:34, Rob wrote: Given a table with some non-unique column theCol, what is the most efficient way to count the number of different values in theCol? I guess you can use a temporary table as a subselect: CREATE TEMPORARY TABLE tmp SELECT UNIQUE theCol FROM theTable; SELECT COUNT(*) FROM tmp; DROP TABLE tmp; But this seem obscenely inefficient- it's a situation just screaming out for a summary function: SELECT COUNT_UNIQUE(theCol) FROM theTable; Does such a function exist in mySQL? Is one planned? Further, using the first method, what are the efficiency and robustness differences between using 'SELECT UNIQUE theCol' and 'SELECT theCol GROUP BY theCol'? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Again regarding MySQL Backup and Recovery
John Hughes wrote: Please allow me to broaden this thread to include backup for mySQL users who have accounts with ISPs. In my case I have mySQL access on a Web server where I have NO access to the server-level options. What is my best option of making a backup of the database and tables that I use? if you can use PHP in your site, then you would use phpMyAdmin. Thats a really handy piece of software. You just put it somewhere in your web via ftp like your other web pages and off you go. Preferably you put it in a password protected directory. It has an interface to mysqldump and lets you down- and upload database-dumps. There is just one problem. Some ISPs limit the run time of a PHP action. This might timeout the script before it can finish the dump. Also, what is my best option if I want to move this database and its tables from one ISP to another? mysqldump can include in the dump all commands to rebuild the database at the new ISP. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Corrupt table content
Hi. I just saw that one of the tables in a pretty big MySQL-database here was corrupted. Checked the table with isamchk (yes, it's an ISD/ISM-database, not MYD/MYI), and found some errors, but corrected them. But, still the content are pretty .. uh, bad, and the backup isn't all that recent... Is there any way to restore this? Does MySQL store some kind of backup of the tables itself? Or do I just have to dump in a backup ? Some neat samples of one of the varchar-fields in the table: \r^?\xa1\xce\xce\x9c^E^X\n\x85:\xe6t\xe2\'k\x 9b\xd3\x89X(\xd4\xd61^G\xf9;C\xb3;Y\xa0\xed\xbfL\xc7?\xfe\xed\xbb\xf7[\xee^U^H\xb0]^O\xdc{\xcf]\xfe\x87 |\xff\'\xfc\xff\x9f\xfe\xfd\xf9g\x99\xa61\xa6?\xca\xced^L\x91\xe4^W\x86L\xff^K^?\xec\xe75\xf9!\xb3\xbe^Y =\x9cv\xcd\xb6\x8c]\n8\x93~\xdc:\x86\xb1\xdez\xc6\xbe\xfe\xe4\xffQ\xbb^?\x95\xfc\xfd\xbf\xa1^?\xb7\xa3\' n\xa8','\xff\xbf\xa9\xe0\xff\xeb^?F1Z\xa6^U\xbf^K\xc5\xb5|\Z\x85F\xc4\xf5\x920\\\xf6X\x80\x8a\x92\xea\xe 8{^P\xe4\xf9\xf0\xff\xf4\x8fz^E\xe7\xcc\xa8`\xf4\x9f\x81\xba^T\xa1:Qc\xac`\xa3\xd8 R\xca^H\xc3+\xf8ay\\9 \xb2-E\xda2\xc6\xf5!6\x9c\xaa(1\xf2\xe1\xb0^Q\xe1\xff\xdb^?\xd7j\xf9\xafD^_\x9b\xab\xb7\xa5\xa9^W\x85h^Y \x93\xd1r^C}g\xa4\x8cb2:\x9a\xc9h%\x93Q\xcc^F^T (there should be a name there ;) Also, can anyone explain what happened here? The database has been running smoothly for a -long- time, no users have access other than select on it, and this just happened out of the blue tonight. -- Trond Arve Nordheim - This message is ROT13-encrypted twice for extra security. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Export Data From Mysql to CSV
Hi How can I export data, from the database tables in Mysql, to a Comma Seperated File(CSV). Urgent Amit Lonkar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
REGULAR EXPRESSIONS
I've a question concerning negations of regular expressions - e.g. I want the sentence this is nice to match, while the sentence this is not nice should not match. I only found possibilities for the negation of single characters on the MySQL-site, but what about whole words?! Greetings, Angela - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Export Data From Mysql to CSV
Hi, SELECT columns INTO OUTFILE 'path/to/directory/file.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY \n FROM table for more info checkout: http://www.mysql.com/doc/S/E/SELECT.html cheers Richard At 11:06 04/03/2002 +, Amit Dilip Lonkar wrote: Hi How can I export data, from the database tables in Mysql, to a Comma Seperated File(CSV). Urgent Amit Lonkar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
table0 LEFT JOIN table1,table2 ?
Hi, Suppose i have a table whith ids and two more tables with logs like this: table0: id1,id2 table1: id1,id2,date,ip table2: id1,id2,date,ip Table0 is where i have all existent pairs (id1,id2). Table1 logs all events of type 1. table2 logs all events of type 2. My problem is that i need to left join table0 to table1 and table2. I'm doing something like: SELECT table0.id1,table0.id2,count(table1.ip),count(table2.ip) FROM table0 LEFT JOIN table1 USING (id1,id2) LEFT JOIN table2 USING (id1,id2,date) .. but this forgets rows i have in table2 that are not in table1. What i want is something like: ___ table1 / left join table0 \___ table2 left join Is it possible ? Thanx, -- dsoares (sql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
REGULAR EXPRESSIONS
Angela, Monday, March 04, 2002, 1:25:44 PM, you wrote: AH I've a question concerning negations of regular expressions - e.g. I AH want the sentence this is nice to match, while the sentence this is AH not nice should not match. AH I only found possibilities for the negation of single characters on the AH MySQL-site, but what about whole words?! What about NOT LIKE statement? For example: SELECT * FROM table1 WHERE text1 LIKE 'this is nice' AND NOT LIKE 'this is not nice'; You can see info about pattern matching at: http://www.mysql.com/doc/P/a/Pattern_matching.html AH Angela -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql_insert_id returns 0 for multiple-row inserts
Michael Villalba writes: mysql_insert_id returns the correct value to my application when I insert a single row using the INSERT...VALUES syntax. However, when I insert multiple rows, it always returns 0. This is inconsistent with the behavior of LAST_INSERT_ID, which returns the id of the first row. Has anyone else observed this behavior? I haven't found anything in the archives. I'm using client 3.23.47. I get the same behavior on win2k and RH 2.2.19. The server is also 3.23.47 and runs on RH 2.2.19. Regards, Michael Hi! Can you make a test case for us, so that we can test it ?? -- 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: Re: Problem with MySQL, ODBC, MS Access
Sorry I´ve tried it but I didn´t succeed. When writing: update tPruefBesch set fdatetime = fTimestamp, datetime and timestamp wont have the same value. I don´t know why, but there is the same fault. I´ve created a small database to show my problem. That´s my table in MySQL 3.23.41. create table tTest (fTimestamp timestamp(14), fCounter int auto_increment not null, fData1 int, fData2 int, primary key (fCounter)); It´s connected via ODBC 3.51 with Access 2000. I´ve installed Service Pack 2 and MDAC 2.70. Here is the description of my database. When you enter Frm_Test you will see three buttons. Button Test 1 will insert value 1 in column data 1. (When you insert a different value auto_increment will count correctly but it is necessary for my database to have the possibility to insert the same value.) Button Test 2 will insert value 1 in column data 1 und value 5 in column data 2. Button Delete first record will delete the first record. So -. When you first click several times on Test 1 you will consider that auto_increment counts correctly. But when you now click eg 10 times on Delete first record and then click eg 12 times on Test 1 you will see that Counter will show 10 times the same number and then go on counting correctly. The next problem is that when you click on Test 1 until auto_increment counts correctly and then click on Test 2 you will see that value 5 is inserted correctly in data 2, but when you delete 10 records it will 10 times insert the value in another recordset. Note that you will not see that the counter has the same value, because of the requery. You will see the counter is counting correctly but the value is inserted at another place. The problem is that the record pointer will not point to the current record after refresh but to another random record. I hope this will help you understanding my problem. Regards, Christine Penkert (See attached file: db3.zip) =?iso-8859-1?Q?db3.zip?= Description: Zip archive - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: mysqld cannot find host.frm even though it is in data/mysql
On Sun, 2002-03-03 at 08:39, root wrote: Description: Down loaded bin/mysqld Ver 3.23.49-max for pc-linux-gnu on i686 from U of Wisc site gunzipped and untarred in /usr/local ran mysql_install_db then ran ./bin/safe_mysqld from install directory kept coming up with error 020303 5:20:41 /usr/local/mysql/bin/mysqld: Can't find file: './mysql/host.frm ' (errno: 13). Did you read the README file? [mle@hal9000 rip]$ perror 13 Error code 13: Permission denied chown -R mysql.mysql mysql/ ought to sort it out. -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
Hi! I'm in the same position as Steve: Considering switching from MyISAM to InnoDB because of the row-level locking capabilities. My application has quite a lot of updates/inserts mixed with selects, and is starting to suffer from the table-locking policy... And like Steve, I'm also scared! :) I've been working quite a lot with MyISAM-tables and I have a good feeling for how it works (backups, data-files, index-files, size of these files, etc) But with InnoDB, I'm - hmmm - confused - that's probably the best word to describe it! The auto-extendible-feature on the todo-list is a great thing! That's one of the big concerns I've had with InnoDB. Another concern is the actual file size - I understand that InnoDB-tables will use a lot more space than the same table in MyISAM... Question is HOW much more (the largest table I currently have is ~2 Gb datafile, 360 Mb indexfile) I'm also confused with how it all works with tablespaces, log-files etc. Is all this common knowledge with transactional databases? If I'm not interested in transactions, do I have to care about all that? And all these limits and parameters! To quote from the InnoDB-documentation: Note that data files must be 2G in some file systems! The combined size of data files must be = 10 MB. The combined size of the log files must be 4G. Hmmm - does any of this put any limitation for my application? (it's a retorical question - I know it depends on my app, but you get the point: It's a lot of new things to learn and consider before switching) And what about backups? Now I'm using mysqldump every night to backup my database - will that be ok for InnoDB too? (I know that it _works_ but what about performance - will it take longer to make a dump with InnoDB-tables? I have to take the website offline while dumping so it's important) I have been looking forward to online-backups, and it is on the todo-list for MySQL 4.1 - will that feature work with InnoDB-tables as well? I noticed that hotbackups is also on the todo-list for InnoDB, but that it will not be free... So... As you can see I'm a bit confused, and I think a lot of people are! It seems to be a whole lot more to learn, more administration, tweaking, etc with InnoDB than with MyISAM. Maybe I'm wrong, but I get that feeling from reading the InnoDB-docs... Maybe some of this confusion and fear could be avoided with a section in the InnoDB documentation that describes InnoDB from a MyISAM-point-of-view :) Explaining how all common things, principles and routines with MyISAM works with InnoDB. I'm sure a lot of people have very good knowledge of MyISAM and comes from the same direction as I do... Thanks for your time! :) /Tobias Steve, I added an item to the TODO list at http://www.innodb.com/todo.html .. May, 2002: Make a data file auto-extendible. You can specify the last data file in innodb_data_file_path like this: ibdata1:50Mautoextend It will create a data file whose initial size is 50 MB, and InnoDB will automatically extend it in units of 10 MB when the data file becomes full. .. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Steve Rapaport [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Sunday, March 03, 2002 12:18 AM Subject: InnoDB frightens me... I'm seriously considering switching to mysql-max so I can make my session handling table an Innodb type. Currently the mysql locking policy allows big traffic jams when several sessions are active simultaneously, and it's the only table that has frequent updates. I need row-locks! BUT, and it's a big but, I just read through the InnoDB manual pages in the mysql site, and it seems I can't have row-locking without a lot of programming and worse, admin overhead. And scary a-priori decisions. At first glance (correct me) I need to 1. Check through all my programs handling this table to add AUTOCOMMIT or Commit/Rollback as appropriate. 2. Decide with zero experience on a lot of maximum sizes which will not be adjustable in future, including dataspace. 3. If I run up against one of those limits in future I am guaranteed a nightmare of table copying, deleting, restoring, and woe if I happen to get a runaway rollback. I am also required to back up my database table and all its update logs in case of this situation, although my chances of restoring them successfully look dim. I am sufficiently frightened to just accept table-lock traffic jams instead. Can anyone tell me how I can use row-locking without getting into this frightening world? Best, Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list
Re: AW: syntax Q
Excuse me for jumping in. UPDATE table SET a=$a 1. I assume the double quotes in your original message are meant to highlight the problem statement. 2. As written, you are attempting to set an integer type column to the string literal consisting of the characters $ and a. MySQL rejects this for either syntax error or type mismatch, whichever comes first in its parsing. 3. When you place the quotes around $a, PHP replaces it in the SELECT statement with the value that the variable $a represents before sending it to MySQL. You'll need to refer to the PHP manual for more detail. I trust you had a restful nap. Doug On Mon, 4 Mar 2002 11:19:05 +0100, Andreas Dau wrote: Thx for ur quick help I will check after a short nap *ggg* -Ursprngliche Nachricht- Von: DL Neil [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 4. Mrz 2002 11:03 An: Andreas Dau Betreff: Re: syntax Q Hello Andreas, I just encountered the following: let $a be a numeric php expression, a the corresponding integer field in a table. When I say: UPDATE table SET a=$a it denies the update. When I say: Update table SET a='$a' it performs the update. Uhm, now I am wondering. Isn't 'something' supposed to be a string? Agreed. What does the SQL CREATE statement look like? What does PHP's assignment of value to $a (and relevant statements leading up to it) look like? Are you sure (the PC thinks) they are both integers? Created with phpmyadmin, column type integer(10). Value to be inserted comes from a method=post script at no point treated other as a numeric... =uh-oh, my theory is looking shaky!? However such conclusion (numeric) may not be the case. Try running the 'form' and inserting a specific integer value, eg 7. Put a debug routine like this into the PHP after the POST is processed and before the MySQL call: echo Value of a appears to be $a~; if ( $a == 7 ) { echo PHP says it is seven; } else { echo PHP says it is not seven; } if ( $a === 7 ) { echo PHP says it is integer seven ; } else { echo PHP says it is not integer seven ; } echo BR; (code not syntax checked) Please advise, =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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Why can't use INDEX while querying?
Hi, all: I create a table as following: CREATE TABLE appsvr_trarte ( rte_id bigint(38) NOT NULL auto_increment, strConfName varchar(70) NOT NULL default '', lFromTime int(16) unsigned NOT NULL default '0', lToTime int(16) unsigned NOT NULL default '0', iSvrType tinyint(2) unsigned NOT NULL default '0', sSrvIP int(10) unsigned NOT NULL default '0', sClitIP int(10) unsigned NOT NULL default '0', iSpeed int(10) unsigned NOT NULL default '0', PRIMARY KEY (rte_id), KEY idx_trarte (lFromTime,sSrvIP,sClitIP) ) TYPE=MyISAM; However, after INSERT into some data( lFromTime,sSrvIP,sClitIP are not unique ), I am astonished by the following: mysql explain SELECT lFromTime ,iSvrType,iSpeed FROM appsvr_trarte where lFromTime = 1009818000 and lFromTime 1017594000; ++--+---+--+-+--++-- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--++-- --+ | appsvr_trarte | ALL | idx_trarte| NULL |NULL | NULL | 168359 | where used | ++--+---+--+-+--++-- --+ 1 row in set (0.00 sec) I have used /usr/local/mysql/bin/myisamchk -a appsvr_trarte , but no effect. I wonder why MySql didn't use index while querying? And how to resolve it? Thank you for any help! B.R. Buding - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: InnoDB frightens me...
Hi :) Are there plans on creating more than on tablesspace plus making them selectable ? So that I can tell the DB: - in which datafiles to store the tablespace X - in which tablespace to store table Y With MYISAM we could simply move the data+index files to a separate disc, create symlinks and everthing was ok, with InnoDB all tables are on the same disc and there is no way to divide the i/o :( Sure, you can use raid. But you still have no say in how much i/o a given table may use. Regards -Ursprüngliche Nachricht- Von: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Gesendet: Sonntag, 3. März 2002 12:46 An: [EMAIL PROTECTED] Betreff: Re: InnoDB frightens me... Steve, I added an item to the TODO list at http://www.innodb.com/todo.html .. May, 2002: Make a data file auto-extendible. You can specify the last data file in innodb_data_file_path like this: ibdata1:50Mautoextend It will create a data file whose initial size is 50 MB, and InnoDB will automatically extend it in units of 10 MB when the data file becomes full. .. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Steve Rapaport [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Sunday, March 03, 2002 12:18 AM Subject: InnoDB frightens me... I'm seriously considering switching to mysql-max so I can make my session handling table an Innodb type. Currently the mysql locking policy allows big traffic jams when several sessions are active simultaneously, and it's the only table that has frequent updates. I need row-locks! BUT, and it's a big but, I just read through the InnoDB manual pages in the mysql site, and it seems I can't have row-locking without a lot of programming and worse, admin overhead. And scary a-priori decisions. At first glance (correct me) I need to 1. Check through all my programs handling this table to add AUTOCOMMIT or Commit/Rollback as appropriate. 2. Decide with zero experience on a lot of maximum sizes which will not be adjustable in future, including dataspace. 3. If I run up against one of those limits in future I am guaranteed a nightmare of table copying, deleting, restoring, and woe if I happen to get a runaway rollback. I am also required to back up my database table and all its update logs in case of this situation, although my chances of restoring them successfully look dim. I am sufficiently frightened to just accept table-lock traffic jams instead. Can anyone tell me how I can use row-locking without getting into this frightening world? Best, Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Perl - mysql
Horváth, Monday, March 04, 2002, 9:51:05 AM, you wrote: HS Hello everyone! HS I would like to use my mysql server via Perl. HS Does anyone send me links or documents about it? Look at Perl DBI interface. You can read more about it here: http://www.mysql.com/doc/P/e/Perl.html You can download this interface from: http://www.mysql.com/downloads/api-dbi.html HS Szabolcs -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
text data cut off at space
Ben, Monday, March 04, 2002, 7:48:45 AM, you wrote: BB I am very new at using MySQL, so this may be a stupid BB question. in my webpage front end, a user logs in, BB and I pass his username to the form throught a hidden BB form. The only problem is that the user name are all BB FirstName LastName. It passes from the login page BB to the php page fine, but once the user enters more BB input data in that php page, and the php page just BB reloads itself when the user submits, the username BB gets cut off at the first space. What's going on? BB thanks Have you inserted first and last name into one field, like 'John Johnson'? What is the type of your field that consists of first and last name? If you insert value of more than maximum column's length into CHAR or VARCHAR column, the value is curtailed to fit. BB Ben -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: query synchronization
Son, Sunday, March 03, 2002, 6:53:36 AM, you wrote: SNIt's like multiple script try to update the same piece of data in SN the mySQL at the same time. SNDo I need to provide a method to synchronize to all my scripts. You can read about 1. LOCK TABLES/UNLOCK TABLES. LOCK TABLES statement lock tables for the current thread. It is used for emulating transactions and to get higher speed for update values. Looc at: http://www.mysql.com/doc/L/O/LOCK_TABLES.html 2. Transactions. You can get some more info about transactions at: http://www.mysql.com/doc/S/E/SET_TRANSACTION.html http://www.mysql.com/doc/I/n/InnoDB_transaction_model.html SN Son Nguyen SN --- Egor Egorov [EMAIL PROTECTED] wrote: Son, Saturday, March 02, 2002, 6:34:37 AM, you wrote: SN Does mySQL synchronization between queries? How does mySQL handle the SN query synchronization. Does anyone know? What do you mean query synchronization ? -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Beginner's Win32
Jesse, Monday, March 04, 2002, 3:11:30 AM, you wrote: JL This is a definite newbie question. I downloaded JL the Win32 version of MySQL to my personal pc JL (windows 98), in order to become familiar with JL the software. As directed by the section in the JL online manual 2.6.2.3 Running MySQL on Windows, JL I deleted the row in the mysql.user table that JL holds Host='localhost' and User='' JL I tried to add a password to the 'root' user, but JL now I can't access MySql. All I get is the JL following error message : What type of error message did you recieve? How did you set password for root user? -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: text data cut off at space
You have to enclose the data in quotes or it cuts off at the first space. Example: $resultID = mysql_query(INSERT INTO data_table (name, address) VALUES ($name, $address),$connect); This will cut off the text at the first space in the text field This is the method that worked for me: $resultID = mysql_query(INSERT INTO data_table (name, address) VALUES ('$name', '$address'),$connect); //notice the quotes I am assuming that the $name and $address are coming from a form. Hope this helps Jon -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] Sent: Monday, March 04, 2002 9:04 AM To: [EMAIL PROTECTED] Subject: text data cut off at space Ben, Monday, March 04, 2002, 7:48:45 AM, you wrote: BB I am very new at using MySQL, so this may be a stupid BB question. in my webpage front end, a user logs in, BB and I pass his username to the form throught a hidden BB form. The only problem is that the user name are all BB FirstName LastName. It passes from the login page BB to the php page fine, but once the user enters more BB input data in that php page, and the php page just BB reloads itself when the user submits, the username BB gets cut off at the first space. What's going on? BB thanks Have you inserted first and last name into one field, like 'John Johnson'? What is the type of your field that consists of first and last name? If you insert value of more than maximum column's length into CHAR or VARCHAR column, the value is curtailed to fit. BB Ben -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
Hi Heikki, I don't know if this has been requested, but what about a tool to 'pre-create' dataspace? This tool would allow someone to create a new dataspace, then a quick restart (After adding the name of the space to 'my.cnf') and the new dataspace is available! Maybe just extract the pieces from MySQL code and make it a separate tool? I know I don't want to have a server shutdown while it creates 10GB of Dataspace! Of course autoextending is one answer. One could just create a small dataspace and let it autoextend. Thanks, Ken - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, March 03, 2002 6:46 AM Subject: Re: InnoDB frightens me... Steve, I added an item to the TODO list at http://www.innodb.com/todo.html .. May, 2002: Make a data file auto-extendible. You can specify the last data file in innodb_data_file_path like this: ibdata1:50Mautoextend It will create a data file whose initial size is 50 MB, and InnoDB will automatically extend it in units of 10 MB when the data file becomes full. .. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Steve Rapaport [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Sunday, March 03, 2002 12:18 AM Subject: InnoDB frightens me... I'm seriously considering switching to mysql-max so I can make my session handling table an Innodb type. Currently the mysql locking policy allows big traffic jams when several sessions are active simultaneously, and it's the only table that has frequent updates. I need row-locks! BUT, and it's a big but, I just read through the InnoDB manual pages in the mysql site, and it seems I can't have row-locking without a lot of programming and worse, admin overhead. And scary a-priori decisions. At first glance (correct me) I need to 1. Check through all my programs handling this table to add AUTOCOMMIT or Commit/Rollback as appropriate. 2. Decide with zero experience on a lot of maximum sizes which will not be adjustable in future, including dataspace. 3. If I run up against one of those limits in future I am guaranteed a nightmare of table copying, deleting, restoring, and woe if I happen to get a runaway rollback. I am also required to back up my database table and all its update logs in case of this situation, although my chances of restoring them successfully look dim. I am sufficiently frightened to just accept table-lock traffic jams instead. Can anyone tell me how I can use row-locking without getting into this frightening world? Best, Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication of temporary tables
On 26 Feb 2002, Arjen Lentz wrote: On Tue, 2002-02-26 at 12:21, Peter Jennings wrote: We have been trying to solve a replication issue related to TEMPORARY tables. Here's the issue Every three hours we run a processX (C++ compiled program) on the Master mysql that goes something like this: CREATE TEMPORARY TABLE tempTableA INSERT INTO tempTableA INSERT INTO normalTableB SELECT * FROM tempTableA processX terminates. Here's what we observe on the Slave side: ERROR: 1146 Table 'MainDatabase.tempTableA' does not exist Slave: error running query 'INSERT normalTableB SELECT * FROM tempTableA Hei We have seen a problem that I am sure has something to do with this too. (Latest test in a 3.23.47 rpm binary from www.mysql.com system) If a program creates and uses a temporary table and something happens with this program (stop, crash) before it sends a 'drop table' to the DB (or finish without sending to the DB a 'drop table' for this temporary table), the slave will stop replication even if the master DB drops all the temporary tables associated to the connection terminated. This error is what you get in the master when this happens : * Slave: query 'drop table prod.test01' partially completed on the master and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; * Sincerely Rafael Martinez - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Creating tables in MySQL++
All, Using MySQL++, how do you check to see whether or not a specific table exist before you go ahead and create it? TIA Thi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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++
All, Other than the MySQL++ documentation found at the mysql home site, where else can I find more examples and/or tutorials of using MySQL++? Thi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FW: C API BLOB type field lengths returned
Have A Great Scouting Day Ken Hylton 7826 Falcon Ridge Drive San Antonio, Texas 78239-4032 210-646-9508 (home) 210-949-7261 (work) 210-949-7254 (fax) 210-287-6756 (cell) [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Kenneth Hylton [mailto:[EMAIL PROTECTED]] mailto:[mailto:[EMAIL PROTECTED]] Sent: Sunday, March 03, 2002 1:19 PM To: '[EMAIL PROTECTED]' Subject:C API BLOB type field lengths returned Howdy - I am using the C API and am returning a result set from a table with a BLOB type item in it. When I populate the BLOB fields initially (with all the same data) the length is returned properly when I issue SELECT * FROM table and decode the result set column metadata. Meaning, that if I put , This Blob's for you! in the column, the value of MYSQL_FIELDS.length = 65535 (max of BLOB type) and MYSQL_FIELDS = 21. When I update a few rows and put, That's OK man, but you still can't have my BLOB! in a few columns, the values for the length of the fields are returned funny, or at least, I don't understand why they are returning they way they are. Meaning the value of the new column length are all set to MYSQL_FIELDS.length = 65535 (again, max of BLOB type) and MYSQL_FIELDS = 49. EVEN ON THE UNCHANGED ROWS! Yuk! A little experimentation showed that if I select on only unchanged rows, the length is returned as 21. But, if the result set has one of the changed rows in it, the length of all blob fields is returned as 49, when (at least to me) it should be 21 or 49. In other words, it returns as the BLOB max_length value the value of the longest blob in the result set, NOT the max_length of each row. I inserted (and updated) some records in the table with BLOB contents It's all about the BLOB and I see the same result. It returns not the length of the BLOB for the row, but the length of the longest BLOB field in the result set. We are running MySQL 3.23.46-Max on RedHat 7.2 Question: 1) How do I get the actual length of the BLOB column in the row, without storing a separate column to maintain BLOB length manually? Ken Hylton 7826 Falcon Ridge Drive San Antonio, Texas 78239-4032 210-646-9508 (home) 210-949-7261 (work) 210-949-7254 (fax) 210-287-6756 (cell) [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with mysql++ under Tru64 Unix
Hello folks, I have a large problem during the compilation of mysql under tru64 unix. I have tried is the 1.7.9 that the 1.7.6a with compiler cxx. The 1.7.9 jam when the following launch the gmake with message: gmake all-recursive gmake[1]: Entering directory `/data/local/src/mysql++-1.7.9' Making all in sqlplusint gmake[2]: Entering directory `/data/local/src/mysql++-1.7.9/sqlplusint' Makefile:222: *** missing separator. Stop. gmake[2]: Leaving directory `/data/local/src/mysql++-1.7.9/sqlplusint' gmake[1]: *** [all-recursive] Error 1 gmake[1]: Leaving directory `/data/local/src/mysql++-1.7.9' gmake: *** [all] Error 2 You can help me? Thanks in advance payment. Erminio Riezzo. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Urgent Help!!!
Hi , When ever I am running my query it gives me 1030- got error 134 from table handler. has anybody come across it. Any help will be greatly appreciated. Thanks Stanley This email and any attachments are confidential. They may contain privileged information and are intended for the named addressee (s) only. They must not be distributed without our consent. If you are not the intended recipient, please notify us immediately and do not disclose, distribute, or retain this email or any part of it. Unless expressly stated, opinions in this email are those of the individual sender, and not of The mobile republic. We believe but do not warrant that this e-mail and any attachments are virus free. You must therefore take full responsibility for virus checking. The mobile republic reserves the right to monitor all email communications through their networks * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 with LEFT JOIN
Hi, please help me with this problem... Suppose i have a table whith ids and two more tables with logs like this: table0: id1,id2 table1: id1,id2,date,ip table2: id1,id2,date,ip Table0 is where i have all existent pairs (id1,id2). Table1 logs all events of type 1. table2 logs all events of type 2. My problem is that i need to left join table0 to table1 and table2. I'm doing something like: SELECT table0.id1,table0.id2,count(table1.ip),count(table2.ip) FROM table0 LEFT JOIN table1 USING (id1,id2) LEFT JOIN table2 USING (id1,id2,date) .. but this forgets rows i have in table2 that are not in table1. What i want is something like: ___ table1 / left join table0 \___ table2 left join Is it possible ? Thanx, -- dsoares (sql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- dsoares (sql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: change column to not null in MySQL
At 9:43 +0800 3/4/02, Al Caponi wrote: Check out the MySQL website for altering table structure: http://www.mysql.com/doc/A/L/ALTER_TABLE.html You could use MODIFY to change the columns definition in your tables. Note that specifying NOT NULL on a column will not affect existing rows where that field already contains a NULL value. Where do you see that? I just took a look at the page and didn't find this mentioned. I just tried the operation, and NULL values were converted to the empty string when I changed the column to NOT NULL. Even if NULL values were retained, it'd probably be best to change them anyway, because future insert operations wouldn't be able to create NULL, and then it might be necessary to look for two special values that signify unknown, rather than just one. Regards, Al -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, March 04, 2002 6:00 AM To: [EMAIL PROTECTED] Subject: change column to not null in MySQL Is there any way in mysql to change a column that contains data from null = yes to be not null. I want to enforce users to enter some value in column_x that was not a required field until now, by setting the column to not accept any record where column_x is null, is there any way to do this without distorting current data (I guess I will have to give some bogus value for all the data field's that are now null before I can change this).? If the column type cannot be changed what's the recommended way of doing something like this. Thanks Shia Shia Meisels BH Photo Video 212-239-7500 Ext. 2158 http://www.bhphotovideo.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: Export Data From Mysql to CSV
At 11:06 + 3/4/02, Amit Dilip Lonkar wrote: Hi How can I export data, from the database tables in Mysql, to a Comma Seperated File(CSV). If you have the FILE privilege, you can use SELECT ... INTO OUTFILE. If you don't, I have a Perl script that can export directly as CSV to a file on the client host, if you want it. Urgent Amit Lonkar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Urgent Help!!!
Hi Stanley You can get mysql to tell you what an error means by doing perror error number In your case the result is 'Record was already deleted (or record file crashed)'. You should be able to recover your table with myisamchk -r james At 15:09 04/03/2002 +, Stanley Joseph wrote: Hi , When ever I am running my query it gives me 1030- got error 134 from table handler. has anybody come across it. Any help will be greatly appreciated. Thanks Stanley This email and any attachments are confidential. They may contain privileged information and are intended for the named addressee (s) only. They must not be distributed without our consent. If you are not the intended recipient, please notify us immediately and do not disclose, distribute, or retain this email or any part of it. Unless expressly stated, opinions in this email are those of the individual sender, and not of The mobile republic. We believe but do not warrant that this e-mail and any attachments are virus free. You must therefore take full responsibility for virus checking. The mobile republic reserves the right to monitor all email communications through their networks * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php James Carrier Bullet Online :: Aim Higher [http://www.bulletonline.com] 41b Beavor Lane, London W6 9BL Tel +44 (0) 20 8834 3442 Fax +44 (0) 20 8741 2790 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ERROR 2006: MySQL Server Has Gone Away
I am having a problem reading a SQL script to recreate a database. I am runing mysql 3.23 on Redhat linux 7.2. The SQL file contains 35 megs worth of data. Everytime i try running it to recreate my database from a backup i get the following error: ERROR 2006: MySQL server has gone away. I beleive that it is failing in the same spot. I have one very large table that has over 1 meg worth of data in a single row. I beleive that is where the SQL is failing. Is there anything I can do to modify the configuration to allow large SQL statements like that to run. Emil Diego Web Coordinator University of Miami School of Business [EMAIL PROTECTED] ph: 305.284.5449 fx: 305.284.3404 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 LEFT JOIN
* ds Hi, please help me with this problem... Suppose i have a table whith ids and two more tables with logs like this: table0: id1,id2 table1: id1,id2,date,ip table2: id1,id2,date,ip Table0 is where i have all existent pairs (id1,id2). Table1 logs all events of type 1. table2 logs all events of type 2. My problem is that i need to left join table0 to table1 and table2. I'm doing something like: SELECT table0.id1,table0.id2,count(table1.ip),count(table2.ip) FROM table0 LEFT JOIN table1 USING (id1,id2) LEFT JOIN table2 USING (id1,id2,date) .. but this forgets rows i have in table2 that are not in table1. What i want is something like: ___ table1 / left join table0 \___ table2 left join Is it possible ? Yes. Use ON instead of USING: SELECT table0.id1,table0.id2,count(table1.ip),count(table2.ip) FROM table0 LEFT JOIN table1 USING (id1,id2) LEFT JOIN table2 ON table2.id1 = table0.id1 and table2.id2 = table0.id2 WHERE ... (If you include a test on the date field you will loose all rows from table2 which are not in table1, as I understand you that is not what you want.) -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with mysql++ under Tru64 Unix
Erminio Efisio Riezzo writes: Hello folks, I have a large problem during the compilation of mysql under tru64 unix. I have tried is the 1.7.9 that the 1.7.6a with compiler cxx. The 1.7.9 jam when the following launch the gmake with message: gmake all-recursive gmake[1]: Entering directory `/data/local/src/mysql++-1.7.9' Making all in sqlplusint gmake[2]: Entering directory `/data/local/src/mysql++-1.7.9/sqlplusint' Makefile:222: *** missing separator. Stop. gmake[2]: Leaving directory `/data/local/src/mysql++-1.7.9/sqlplusint' gmake[1]: *** [all-recursive] Error 1 gmake[1]: Leaving directory `/data/local/src/mysql++-1.7.9' gmake: *** [all] Error 2 You can help me? Thanks in advance payment. Erminio Riezzo. Hi! You did not specify do you use Compaq or GNU compiler ?? In any case, run automake (ignore warnings), autoconf, configure and then GNU make. -- 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
Selecting from within date range
torkil, Monday, March 04, 2002, 10:57:50 AM, you wrote: tj I have a database table containing log files that are written by tj different individuals almost every day. tj So at any given time I want to be able to go through a web tj interface and get a list over logs written in any given month. tj People writing in this log also work in different department, tj a field also included in the log. tj Timestamps for the log is stored as datetime on the format tj -MM-DD HH:MM:SS tj So I want to make the following selection: tj Show me all log entries for department X for the given month tj So how would I go about to do write this query in the easiest tj way, given that I provide the year and month I want to view tj before the query is made? tj select * from LOG_TABLE where DEPARTMENT='X' . . . ??? There are many date and time functions, it will be useful for you. Look at: http://www.mysql.com/doc/D/a/Date_and_time_functions.html tj - Torkil -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Why can't use INDEX while querying?
Buding, Monday, March 04, 2002, 3:37:08 PM, you wrote: BC Hi, all: BC I create a table as following: BC CREATE TABLE appsvr_trarte ( BC rte_id bigint(38) NOT NULL auto_increment, BC strConfName varchar(70) NOT NULL default '', BC lFromTime int(16) unsigned NOT NULL default '0', BC lToTime int(16) unsigned NOT NULL default '0', BC iSvrType tinyint(2) unsigned NOT NULL default '0', BC sSrvIP int(10) unsigned NOT NULL default '0', BC sClitIP int(10) unsigned NOT NULL default '0', BC iSpeed int(10) unsigned NOT NULL default '0', BC PRIMARY KEY (rte_id), BC KEY idx_trarte (lFromTime,sSrvIP,sClitIP) BC ) TYPE=MyISAM; BC However, after INSERT into some data( lFromTime,sSrvIP,sClitIP are not BC unique ), I am astonished by the following: BC mysql explain SELECT lFromTime ,iSvrType,iSpeed FROM appsvr_trarte where BC lFromTime = 1009818000 and lFromTime 1017594000; BC ++--+---+--+-+--++-- BC --+ BC | table | type | possible_keys | key | key_len | ref | rows | BC Extra | BC ++--+---+--+-+--++-- BC --+ BC | appsvr_trarte | ALL | idx_trarte| NULL |NULL | NULL | 168359 | BC where used | BC ++--+---+--+-+--++-- BC --+ BC 1 row in set (0.00 sec) BC I have used /usr/local/mysql/bin/myisamchk -a appsvr_trarte , but no BC effect. BC I wonder why MySql didn't use index while querying? And how to resolve BC it? MySQL doesn't use indexes if result of query is more than 30% rows in the table. It works much faster without using indexes. You can read about how MySQL uses indexes at: http://www.mysql.com/doc/M/y/MySQL_indexes.html BC B.R. BC Buding -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
database= in my.cnf causes mysqldump to fail
Viraj, Thursday, February 28, 2002, 9:58:32 PM, you wrote: VA I'm not sure if this is a bug or not, but I've noticed that if I have a VA 'database=' in my ~/.my.cnf, calling mysqldump with any possible options VA always returns: VA mysqldump: option `--databases' doesn't allow an argument VA The only way I can get it working is to remove the 'database=' line from VA ~/.my.cnf. Is this normal? Yes, you can set [mysqldump] databases and then you can specify more than one database in command-line. VA Viraj. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LOAD DATA INFILE and how to ignore garbage lines at end of load file?
I'm exporting data from Oracle and importing it into MySQL. The problem is Oracle puts garbage lines at the end of it's output files. Lines like 300 rows selected and input truncated to 9 chars as well as empty lines. When MySQL loads these files, I'm getting rows inserted for the empty lines at the end of these files. Can I get MySQL to ignore empty lines at the end of these files? or if anyone has Oracle experience can I get it to suppress the output of these line? Thanks, Rich Rich Bolen Senior Software Developer GretagMacbeth Advanced Technologies Center 79 T. W. Alexander Drive - Bldg. 4401 - Suite 250 PO Box 14026 Research Triangle Park, North Carolina 27709-4026 USA Phone: 919-549-7575 x239, Fax: 919-549-0421 http://www.gretagmacbeth.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Dynamic SQL
Using UltraDev 4 with Phakt 1.2.1, linking to a MySQL server 3.22.23 I want to develop a SQL SELECT query where I can vary what customers are listed depending on the value of various form fields. For example if the check box surname_all is ticked then all customers are displayed if not then it displays all customers where the surname lies between surname_from to surname_to. I couldn't find an appropriate way of handling this in MySQL directly so my aim was to determine the SQL portion outside the query and then append it to the query: if ($surname_all == -1){ $surname_query = WHERE surname between $surname_from and $surname_to; } else { $surname_query = ; } NB - If $surname_all = -1 then it has been ticked. And then I would declare my SQL statement as SELECT * from customers.$surname_query UltraDev doesn't like this and so I would appreciate any help in either a better way to code it in UltraDev or a SQL statement that would achieve the same aim. Thanks for your help - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with mysql++ under Tru64 Unix
Dear Sinisa, thanks for the fast answer. I use the GCC-3.0.3 and have already made automake, autoconf and ./configure. When I make gmake jams on the error that I have to you before marked. I use gmake the 3.79.1, automake the 1.5 and autoconf the 2.52. Thanks still for the kind aid. Erminio Riezzo. - Original Message - From: Sinisa Milivojevic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, March 04, 2002 4:33 PM Subject: Re: Problem with mysql++ under Tru64 Unix Erminio Efisio Riezzo writes: Hello folks, I have a large problem during the compilation of mysql under tru64 unix. I have tried is the 1.7.9 that the 1.7.6a with compiler cxx. The 1.7.9 jam when the following launch the gmake with message: gmake all-recursive gmake[1]: Entering directory `/data/local/src/mysql++-1.7.9' Making all in sqlplusint gmake[2]: Entering directory `/data/local/src/mysql++-1.7.9/sqlplusint' Makefile:222: *** missing separator. Stop. gmake[2]: Leaving directory `/data/local/src/mysql++-1.7.9/sqlplusint' gmake[1]: *** [all-recursive] Error 1 gmake[1]: Leaving directory `/data/local/src/mysql++-1.7.9' gmake: *** [all] Error 2 You can help me? Thanks in advance payment. Erminio Riezzo. Hi! You did not specify do you use Compaq or GNU compiler ?? In any case, run automake (ignore warnings), autoconf, configure and then GNU make. -- 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: ERROR 2006: MySQL Server Has Gone Away
Hello Emil You need to increase the max packet size that mysql can handle... try putting this in your my.cnf file under [mysqld]: set-variable = max_allowed_packet=5M and restarting your server (obv. if you're starting mysql from the command line use the switch --max_allowed_packet) james At 10:35 04/03/2002 -0500, Diego, Emil wrote: I am having a problem reading a SQL script to recreate a database. I am runing mysql 3.23 on Redhat linux 7.2. The SQL file contains 35 megs worth of data. Everytime i try running it to recreate my database from a backup i get the following error: ERROR 2006: MySQL server has gone away. I beleive that it is failing in the same spot. I have one very large table that has over 1 meg worth of data in a single row. I beleive that is where the SQL is failing. Is there anything I can do to modify the configuration to allow large SQL statements like that to run. Emil Diego Web Coordinator University of Miami School of Business [EMAIL PROTECTED] ph: 305.284.5449 fx: 305.284.3404 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php James Carrier Bullet Online :: Aim Higher [http://www.bulletonline.com] 41b Beavor Lane, London W6 9BL Tel +44 (0) 20 8834 3442 Fax +44 (0) 20 8741 2790 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
profiler...
Does anyone know if there is a good profiler to use on MySql, for example, in SQL Server, you can start the 'profiler' and it will tell you everything that is happening on the server. Thanks, Eddie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication code from 3.23.25-beta to today.
Hello, I'm looking to port a MySQL replication set that runs 3.23.25-beta to a later version of MySQL. I understand that the format for the binary log changed from .25 to .26 and again at 4. It appears, from looking at the code for 4, that it looks at the version of the replication master and if it's a 3.x master it will use the appropriate format. I've yet to find the correct logfile structure but would like to work with the version 4 code to make it check for the really-old version of the binary log and work with it. Therefore, can someone point me to the logfile structures? Am I attempting the impossible by trying to get version 4 to read a binary log of 3.23.25? Thanks for any help and pointers in the right direction. Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Problems - Peak loads - Two processors
We have FreeBSD 4.2-RELEASE and I can't say for sure how much time it might take to upgrade to 4.3 or higher. It might take anywhere from one reboot to several hours (if something won't work). Mysql was built on our server. Sincerely, Stephen -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 03, 2002 5:24 PM To: Dreamtime.net Inc. Cc: Mysql@Lists. Mysql. Com Subject: Re: Mysql Problems - Peak loads - Two processors On Sun, Mar 03, 2002 at 01:25:46PM -0800, Dreamtime.net Inc. wrote: Hi, We have mysql 3.23.43 running under FreeBSD 4.2, two-processors and a Tyan LE S2510 motherboard with 1 gig of ram. After a few peak loads of the server (load avg. gets higher than 20), mysql starts eating processor cycles. Server load gets close to 1.0, mysqld is active all the time, although almost no queries comes in. Has anyone had this problem before? Any help is highly appreciated. I've seen problems with MySQL on FreeBSD older than 4.3. Can you upgrade to 4.3 or newer to see if that helps? Also, are you using your own build, one from ports, or the official MySQL binary? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 24 days, processed 806,316,132 queries (381/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA INFILE and how to ignore garbage lines at end ofload file?
At 10:58 -0500 3/4/02, Richard Bolen wrote: I'm exporting data from Oracle and importing it into MySQL. The problem is Oracle puts garbage lines at the end of it's output files. As you've noted, the problem is Oracle. If you're using Unix, you could use tail to see how many of these lines there are and wc to count the total number of lines in the file. With that information, you can construct the proper value of n and use head -n to get only the initial part of the file that contains the non-garbage lines. Or you could reverse the order of the lines in the file (expensive?) and then use IGNORE n LINES in your LOAD DATA statement to ignore the first n lines. Better if you can get Oracle just to suppress these lines in the first place. Perhaps someone else will have a suggestion how to do that. Lines like 300 rows selected and input truncated to 9 chars as well as empty lines. When MySQL loads these files, I'm getting rows inserted for the empty lines at the end of these files. Can I get MySQL to ignore empty lines at the end of these files? or if anyone has Oracle experience can I get it to suppress the output of these line? Thanks, Rich Rich Bolen Senior Software Developer GretagMacbeth Advanced Technologies Center 79 T. W. Alexander Drive - Bldg. 4401 - Suite 250 PO Box 14026 Research Triangle Park, North Carolina 27709-4026 USA Phone: 919-549-7575 x239, Fax: 919-549-0421 http://www.gretagmacbeth.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Dynamic SQL
Try using something a little different: if ($surname_all){ //checks to see if the variable is set $surname_query = WHERE surname between $surname_from and $surname_to; } else { $surname_query = ; } -Original Message- From: Craig Shepherd [mailto:[EMAIL PROTECTED]] Sent: Monday, March 04, 2002 10:55 AM To: [EMAIL PROTECTED] Subject: Dynamic SQL Using UltraDev 4 with Phakt 1.2.1, linking to a MySQL server 3.22.23 I want to develop a SQL SELECT query where I can vary what customers are listed depending on the value of various form fields. For example if the check box surname_all is ticked then all customers are displayed if not then it displays all customers where the surname lies between surname_from to surname_to. I couldn't find an appropriate way of handling this in MySQL directly so my aim was to determine the SQL portion outside the query and then append it to the query: if ($surname_all == -1){ $surname_query = WHERE surname between $surname_from and $surname_to; } else { $surname_query = ; } NB - If $surname_all = -1 then it has been ticked. And then I would declare my SQL statement as SELECT * from customers.$surname_query UltraDev doesn't like this and so I would appreciate any help in either a better way to code it in UltraDev or a SQL statement that would achieve the same aim. Thanks for your help - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
foreign key problem
hello i creates two table t1 and t2; CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; it supports the foreign key fine. if i will alter the table t2 and add one more column to it , then foreign key is not working . if anybody knows about this or it is a bug of MySQL ? Regards Asit - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Dynamic SQL
Thanks for that. It's actually more the UltraDev/Phakt thing I'm having problems with. I just wondered how you would set the SQL statement in such a way that UltraDev wouldn't complain - it doesn't like SELECT * from customers.$surname_query. Do you know of a good way of handling conditional statements in MySQL? -Original Message- From: Trelfa, Jonathon [mailto:[EMAIL PROTECTED]] Sent: 04 March 2002 16:34 To: [EMAIL PROTECTED] Subject: RE: Dynamic SQL Try using something a little different: if ($surname_all){ //checks to see if the variable is set $surname_query = WHERE surname between $surname_from and $surname_to; } else { $surname_query = ; } -Original Message- From: Craig Shepherd [mailto:[EMAIL PROTECTED]] Sent: Monday, March 04, 2002 10:55 AM To: [EMAIL PROTECTED] Subject: Dynamic SQL Using UltraDev 4 with Phakt 1.2.1, linking to a MySQL server 3.22.23 I want to develop a SQL SELECT query where I can vary what customers are listed depending on the value of various form fields. For example if the check box surname_all is ticked then all customers are displayed if not then it displays all customers where the surname lies between surname_from to surname_to. I couldn't find an appropriate way of handling this in MySQL directly so my aim was to determine the SQL portion outside the query and then append it to the query: if ($surname_all == -1){ $surname_query = WHERE surname between $surname_from and $surname_to; } else { $surname_query = ; } NB - If $surname_all = -1 then it has been ticked. And then I would declare my SQL statement as SELECT * from customers.$surname_query UltraDev doesn't like this and so I would appreciate any help in either a better way to code it in UltraDev or a SQL statement that would achieve the same aim. Thanks for your help - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Please Help! MySql 4.01/PhP 4.06 looking for Libmysqlclient.so.10?
John Dean [EMAIL PROTECTED] writes: Hi All you need to do is include a symlink on libmysqlclient.so.11 to point to libmysqlclient.so.10 e.g ln libmysqlclient.so.11 libmysqlclient.so.10 Not a good suggestion for libraries in general. If a library so name has changed, it means it not compatible. Install the older library in addition -- Trond Eivind Glomsrød Red Hat, 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: Perl - mysql
--- Horváth_Szabolcs [EMAIL PROTECTED] wrote: Hello everyone! I would like to use my mysql server via Perl. Does anyone send me links or documents about it? Thanks Szabolcs I am using DBI module in Perl to connect to mySQL database. More information can be found at the below link http://dbi.symbolstone.org/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Sports - sign up for Fantasy Baseball http://sports.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
VARCHAR/CHAR problem
I give this query: ALTER TABLE `companies` ADD `Tel3` CHAR(30) BINARY but it creates tel3 as VARCHAR(30) ! Why? Makis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: www.fcliestal.ch
This has nothing to do with MySQL. Check out freesql.org and talk to them about your problems. On Monday 04 March 2002 1:31 am, Zandona Marcello BGV wrote: sql-problem! hello, please could you help me? after you made some work on your server.. the side of www.fcliestal.chdoes not work anymore because i did not change anything on it, i do not know, what to do... please could you reactivate the site? ... or at least give me some information what i have to do thank you very much, for your answer...! greetings from switzerland marcello zandona - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
extra max() function possibly very useful?
Hi, What would the plausability of a function like this being implemented in the future. create table mytable (id int, val char(255), hits int); insert some data... select max(5,hits) from mytable group by id; This would allow selecting of the top 5 rows for each id according to the hit column. Without this function it means the code must be written manually as, foreach(id) { select * from mytable where id='$id' order by hits desc limit 5 } which is obviously quite inefficient where the domain of id becomes large. Any input from the developers about the possibility of this functionality would be much appreciated. Richard p.s. here is the word mysql to get past the intelligent spam filter. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: extra max() function possibly very useful?
On Monday 04 March 2002 10:50 am, Richard Clarke wrote: create table mytable (id int, val char(255), hits int); insert some data... select max(5,hits) from mytable group by id; This would allow selecting of the top 5 rows for each id according to the hit column. Without this function it means the code must be written manually as, foreach(id) { select * from mytable where id='$id' order by hits desc limit 5 } Why would you do your select like that instead of: select * from mytable order by hits desc limit 5 ? And for the spam filter: mysql, microsoft sucks, nusphere lawsuit. :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: extra max() function possibly very useful?
Because that wouldn't give the correct results. I want the top 5 rows for EACH id. A short example would be, mytable: IdValHits 1 a 10 1 b 15 1 c 17 2 q 200 2 r 205 2 s 101 2 t 50 3 zz 10 3 yy 20 3 xx 30 3 ww 40 3 uu 50 select max(2,hits) from mytable Id 1 b 15 1 c 17 2 q 200 2 r 205 3 ww 40 3 uu 50 This is the top two rows for EACH id... without this functionality i have to do select distinct cid from mytable; foreach(cid) { select * from mytable where cid='$cid' order by hits desc limit 2; } Which means the table is reread over and over. With this extra function we could reduce the reads to at best once. Richard - Original Message - From: Christopher Thompson [EMAIL PROTECTED] To: Richard Clarke [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 04, 2002 5:56 PM Subject: Re: extra max() function possibly very useful? On Monday 04 March 2002 10:50 am, Richard Clarke wrote: create table mytable (id int, val char(255), hits int); insert some data... select max(5,hits) from mytable group by id; This would allow selecting of the top 5 rows for each id according to the hit column. Without this function it means the code must be written manually as, foreach(id) { select * from mytable where id='$id' order by hits desc limit 5 } Why would you do your select like that instead of: select * from mytable order by hits desc limit 5 ? And for the spam filter: mysql, microsoft sucks, nusphere lawsuit. :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: InnoDB frightens me...
Christian Rabe writes: Are there plans on creating more than on tablesspace plus making them selectable ? So that I can tell the DB: - in which datafiles to store the tablespace X - in which tablespace to store table Y This is one of the things I was wondering about.. If I have multiple InnoDB tablespaces, and I fire up the MySQL command-line client and issue a Create Table... TYPE=InnoDB, how do I tell it I want the table created in InnoDB space #1 or #2...?? As far as I can tell, it would appear that MySQL/InnoDB get the deciding vote there.. Perhaps I'm just missing something.. -- Rick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
win start problem
Hi All, New to mysql. Running: Win2k as Administrator mysql-3.23.49-win.zip (binaries) Keep getting error (complete output below sig): mysqld: Table 'mysql.host' doesn't exist I've tried running each binary msqld version (mysqld, mysql-max). I haven't seen any solutions to this problem on the web (google) other than one person suggesting to someone with the same error that they didn't have privileges to write the table. And another saying they needed to re-run mysql_install_db ( I didn't get this with the binary dist.) At a loss... -Ryan [My first run] C:\mysql\binmysqld-max-nt --standalone --console InnoDB: The first specified data file c:\_data\mysql\innodb\ibdata1 did not exis t: InnoDB: a new database to be created! 020301 14:17:38 InnoDB: Setting file c:\_data\mysql\innodb\ibdata1 size to 400 MB InnoDB: Database physically writes the file full: wait... 020301 14:18:16 InnoDB: Log file c:\_data\mysql\innodb\ib_logfile0 did not exis t: new to be created InnoDB: Setting log file c:\_data\mysql\innodb\ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 020301 14:18:16 InnoDB: Log file c:\_data\mysql\innodb\ib_logfile1 did not exis t: new to be created InnoDB: Setting log file c:\_data\mysql\innodb\ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... 020301 14:18:17 InnoDB: Log file c:\_data\mysql\innodb\ib_logfile2 did not exis t: new to be created InnoDB: Setting log file c:\_data\mysql\innodb\ib_logfile2 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 020301 14:18:22 InnoDB: Started - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: VARCHAR/CHAR problem
At 15:39 +0200 3/4/02, savaidis wrote: I give this query: ALTER TABLE `companies` ADD `Tel3` CHAR(30) BINARY but it creates tel3 as VARCHAR(30) ! You have other variable-length columns in your table. http://www.mysql.com/doc/S/i/Silent_column_changes.html Why? Makis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
accessing all dbs through one odbc connection..
I need to have access to all the databases on my server using one ODBC connection. Currently, I have to specify the db and only have access to that one...is there a way around this? How about SQL Links? Thanks, Eddie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql and db2
Currently ezmlm supports mysql, I was wondering if there was a way to get db2 to talk to mysql, so that when someone subscribes to the mailing list, their credentials get passed to db2. Thanks for any help. Arthur - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: LOAD DATA INFILE and how to ignore garbage lines at end ofload file?
I added the line set feedback off at the beginning of my Oracle report script and that suppressed the output of the garbage lines. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Monday, March 04, 2002 11:33 AM To: Richard Bolen; MySQL Mailing List (E-mail) Subject: Re: LOAD DATA INFILE and how to ignore garbage lines at end ofload file? At 10:58 -0500 3/4/02, Richard Bolen wrote: I'm exporting data from Oracle and importing it into MySQL. The problem is Oracle puts garbage lines at the end of it's output files. As you've noted, the problem is Oracle. If you're using Unix, you could use tail to see how many of these lines there are and wc to count the total number of lines in the file. With that information, you can construct the proper value of n and use head -n to get only the initial part of the file that contains the non-garbage lines. Or you could reverse the order of the lines in the file (expensive?) and then use IGNORE n LINES in your LOAD DATA statement to ignore the first n lines. Better if you can get Oracle just to suppress these lines in the first place. Perhaps someone else will have a suggestion how to do that. Lines like 300 rows selected and input truncated to 9 chars as well as empty lines. When MySQL loads these files, I'm getting rows inserted for the empty lines at the end of these files. Can I get MySQL to ignore empty lines at the end of these files? or if anyone has Oracle experience can I get it to suppress the output of these line? Thanks, Rich Rich Bolen Senior Software Developer GretagMacbeth Advanced Technologies Center 79 T. W. Alexander Drive - Bldg. 4401 - Suite 250 PO Box 14026 Research Triangle Park, North Carolina 27709-4026 USA Phone: 919-549-7575 x239, Fax: 919-549-0421 http://www.gretagmacbeth.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
storing files in mySQL
Hi everyone, I'd like to know how to load files, such as MS-Word documents or PDF documents, in a mySQL database. I've setup a blob type in a table, but where do I go from there? The purpose of this is to store files uploaded from a web page and processed with Java servlets. Then, the files should be retrievable (displayed or downloaded) back to a client browser. My environment: Apache Web server 1.3.x on Solaris 2.8 with Java servlets environment. Thanks for any help, Dang Nguyen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: extra max() function possibly very useful?
Correct. Richard - Original Message - From: DL Neil [EMAIL PROTECTED] To: Richard Clarke [EMAIL PROTECTED]; Christopher Thompson [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 04, 2002 6:34 PM Subject: Re: extra max() function possibly very useful? LIMIT to be applied to each group result, instead of to the final resultset. Correct? =dn Because that wouldn't give the correct results. I want the top 5 rows for EACH id. ... select distinct cid from mytable; foreach(cid) { select * from mytable where cid='$cid' order by hits desc limit 2; } Which means the table is reread over and over. With this extra function we could reduce the reads to at best once. SQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Many processes in SHOW PROCESSLIST;
On Mon, Mar 04, 2002 at 11:19:54AM +0100, Takacs Istvan wrote: Hi, We use mysql 1.23.47 as a database for our PHP based accounting system. We use mysql_pconnect() to get a connection to the DB. This morning we couldn't log in because mysql sent back a 'too many open connection!' (or someting like that) error message. If I use the command SHOW PROCESSLIST than I can see many 'Sleep' processes. If I try to kill some processes by KILL processnumber than newer processes appear in the list. Sounds like you need to decrease the amount of time that idle connections stay around in the server before they are disconnected. Is there any way to kill all of the hanging processes? Best not to get them in the first place! And why are so many processes if we use mysql_pconnect() which uses the same connection what the PHP user opened before? mysql_pconnect() tells PHP *not* to close the connection when a page completes. That means there will be more open connections to the database server. If you have a sufficient number of Apache (or whatever) children, you can easily over-run MySQL. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 25 days, processed 840,969,833 queries (386/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: REGULAR EXPRESSIONS
On Mon, Mar 04, 2002 at 12:25:44PM +0100, Angela Harneit wrote: I've a question concerning negations of regular expressions - e.g. I want the sentence this is nice to match, while the sentence this is not nice should not match. I only found possibilities for the negation of single characters on the MySQL-site, but what about whole words?! I don't see how negation fits in here. In fact, it doesn't sound like you need a regular expression at all--unless there's more to this that you're telling. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 25 days, processed 841,062,018 queries (386/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA LOCAL INFILE in Alpha 3.23.49
Hi! Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes: Sinisa [EMAIL PROTECTED] writes: Description: LOAD DATA LOCAL INFILE ... leads to 'The used command is not allowed with this MySQL version' This happens regardless of whether I have a local-infile = 1 in my /etc/my.cnf. In neither case does the local-infile variable show up with SHOW VARIABLES. We don't have a status variable that shows if this feature is enabled or not. The above is quite strange as I did test this properly before doing a release. Did you give the above option to both 'mysqld' and 'mysql' ? Which MySQL distribution are you using ? Sinisa Hi! Sinisa A new startup variable : Sinisa --local-infile Sinisa must be used to enable the above feature. That is what the user described that he did do... Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA INFILE and how to ignore garbage lines at end of load file?
Is this a true export? Because I've never had garbage lines in my files. Dave On Mon, Mar 04, 2002 at 10:33:14AM -0600, Paul DuBois wrote: At 10:58 -0500 3/4/02, Richard Bolen wrote: I'm exporting data from Oracle and importing it into MySQL. The problem is Oracle puts garbage lines at the end of it's output files. As you've noted, the problem is Oracle. If you're using Unix, you could use tail to see how many of these lines there are and wc to count the total number of lines in the file. With that information, you can construct the proper value of n and use head -n to get only the initial part of the file that contains the non-garbage lines. Or you could reverse the order of the lines in the file (expensive?) and then use IGNORE n LINES in your LOAD DATA statement to ignore the first n lines. Better if you can get Oracle just to suppress these lines in the first place. Perhaps someone else will have a suggestion how to do that. Lines like 300 rows selected and input truncated to 9 chars as well as empty lines. When MySQL loads these files, I'm getting rows inserted for the empty lines at the end of these files. Can I get MySQL to ignore empty lines at the end of these files? or if anyone has Oracle experience can I get it to suppress the output of these line? Thanks, Rich Rich Bolen Senior Software Developer GretagMacbeth Advanced Technologies Center 79 T. W. Alexander Drive - Bldg. 4401 - Suite 250 PO Box 14026 Research Triangle Park, North Carolina 27709-4026 USA Phone: 919-549-7575 x239, Fax: 919-549-0421 http://www.gretagmacbeth.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: extra max() function possibly very useful?
Richard, There has been talk of this before. (I haven't checked but) wouldn't be surprised if it's on the ToDo list - did you know that such info is available within the manual? If it's not, then propose such as a 'wish list'. Regards, =dn - Original Message - From: Richard Clarke [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 04 March 2002 19:09 Subject: Re: extra max() function possibly very useful? Correct. Richard - Original Message - From: DL Neil [EMAIL PROTECTED] To: Richard Clarke [EMAIL PROTECTED]; Christopher Thompson [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 04, 2002 6:34 PM Subject: Re: extra max() function possibly very useful? LIMIT to be applied to each group result, instead of to the final resultset. Correct? =dn Because that wouldn't give the correct results. I want the top 5 rows for EACH id. ... select distinct cid from mytable; foreach(cid) { select * from mytable where cid='$cid' order by hits desc limit 2; } Which means the table is reread over and over. With this extra function we could reduce the reads to at best once. SQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Deleting duplicate records
Hi to all, I'm new using mysql. I'm trying to load data from a text file to one of my table. This text file contains duplicate records. My question is how do you tell mysql to delete duplicate records and load just the ones that are not duplicate? I already tried the REPLACE and IGNORE key words but is I dont get the result that I want. Any idea Thanks in advanced. Nato - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Many processes in SHOW PROCESSLIST;
At 01:13 PM 3/4/2002 , you wrote: On Mon, Mar 04, 2002 at 11:19:54AM +0100, Takacs Istvan wrote: Hi, We use mysql 1.23.47 as a database for our PHP based accounting system. We use mysql_pconnect() to get a connection to the DB. This morning we couldn't log in because mysql sent back a 'too many open connection!' (or someting like that) error message. If I use the command SHOW PROCESSLIST than I can see many 'Sleep' processes. If I try to kill some processes by KILL processnumber than newer processes appear in the list. Sounds like you need to decrease the amount of time that idle connections stay around in the server before they are disconnected. Is there any way to kill all of the hanging processes? Best not to get them in the first place! And why are so many processes if we use mysql_pconnect() which uses the same connection what the PHP user opened before? mysql_pconnect() tells PHP *not* to close the connection when a page completes. That means there will be more open connections to the database server. If you have a sufficient number of Apache (or whatever) children, you can easily over-run MySQL. Jeremy Jeremy, PMFJI, but has anyone done any testing to see if persistent connections with MySQL and PHP is actually faster in practice? Brent _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: extra max() function possibly very useful?
Spambusting: MySQL Richard, There has been talk of this before. (I haven't checked but) wouldn't be surprised if it's on the ToDo list - did you know that such info is available within the manual? If it's not, then propose such as a 'wish list'. Regards, =dn - Original Message - From: Richard Clarke [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 04 March 2002 19:09 Subject: Re: extra max() function possibly very useful? Correct. Richard - Original Message - From: DL Neil [EMAIL PROTECTED] To: Richard Clarke [EMAIL PROTECTED]; Christopher Thompson [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 04, 2002 6:34 PM Subject: Re: extra max() function possibly very useful? LIMIT to be applied to each group result, instead of to the final resultset. Correct? =dn Because that wouldn't give the correct results. I want the top 5 rows for EACH id. ... select distinct cid from mytable; foreach(cid) { select * from mytable where cid='$cid' order by hits desc limit 2; } Which means the table is reread over and over. With this extra function we could reduce the reads to at best once. SQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Can't restart MySQL after innodb table filled up
I'm having problems restarting a MySQL server after a certain table reached maximum capacity. I managed to dump my entire database to a flat file before I restarted the server however when I tried to re-import my data the operation failed. My database size set to 500M in the innodb section of my my.cnf file. I'm running RH 7.2, kernel 2.4.19pre1 with 512M of RAM and have rebooted the server without any change in results. I've also upgraded MySQL-Max from 3.23.48 to 3.23.49a using the RPMS on mysql.com. I've tried removing all the innodb files within my database directory and MySQL seems to build the new 500M file properly but then crashes with the following message in the mysql.log: 020304 11:33:01 mysqld started 020304 11:33:02 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 1819350038 InnoDB: Assertion failure in thread 4096 in file ../include/buf0buf.ic line 265 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=268431360 record_buffer=1044480 sort_buffer=4194296 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 773739 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffe0b8, stack_bottom=0x49043190, thread_stack=65536, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x47f89f20 is invalid pointer thd-thread_id=138727664 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 138727664 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash 020304 11:33:02 mysqld ended - I've also tried changing a few values in the my.cnf file including changing the number of threads to 1 and increasing the size of the DB to 600M. Is this a known bug? Thanks, -- Erik Barker Sr. Systems Engineer NetNation Communications Inc. http://www.netnation.com | http://www.domainpeople.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Deleting duplicate records
On Mon, Mar 04, 2002 at 02:48:06PM -0500, Natividad Castro wrote: I'm new using mysql. I'm trying to load data from a text file to one of my table. This text file contains duplicate records. My question is how do you tell mysql to delete duplicate records and load just the ones that are not duplicate? I already tried the REPLACE and IGNORE key words but is I dont get the result that I want. Any idea If the data is in a text file, elimiate the duplicates before MySQL is even involved: cat myfile.txt | sort | uniq nodups.txt Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 25 days, processed 842,341,115 queries (386/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Web server performance
I am currently implementing a database-ran webserver dll: Info: Ms Visual C++ MySQL ++ 1.7.1 MySQL-nt 3.23.47 MyISAM db. 3or 4 Queries in the application Biggest Query: 66,000 records using LIKE something% Question: I am currently using a connection every time I query, but since this is a static dll, can I somehow keep a connection open for all the queries? I have tried just opening a connection before(one connection for the app, all threads use it), but sooner or later, the queries start causing exceptions and then returning no error until the dll faults because the connection closes. Is there a way to tell the database that the connection needs to handle multiple queries in a parrallel manner? Any other speed tweaks if I have to open a different connection for each query? Thanks in advance, Jeremy Beha - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Slient install - Win32 Mysql
I have the same problem (silent install). In details: I run setup.exe with command line options -r to create response file needed for silent installation in InstallShield. By default it is created in the windows directory with name setup.iss. Then I move this setup.iss to the same folder as setup.exe and try to run setup.exe -s to perform the silent setup, but it fails. The log file contains: [InstallShield Silent] Version=v4.90.000 File=Log File [ResponseResult] ResultCode=-3 According to IS documentation: -3 Required data not found in the Setup.iss file. Even if I set verbose this is all I have. I didn't find the source for the InstallShield project in the source distribution (I searched for *.ipr, *.rul). What a pity! With the IS project source in hand it would be a piece of cake to handle this situation. BTW this silent setup thing is working with another software (I tryed it with J2RE 1.4.0 - I had to collect the exracted stuff from the temp directory during installation but then it worked all right). Maybe there's a bug in the InstallShield version used (it is an old 5.x version). Maybe there's a bug in the IS project for MySql. Maybe I'm doing something wrong. But I guess it makes *no sense* that everyone writes his/her own silent setup for MySql when there is an official one already. Thanks, Attila -Original Message- From: Gerald R. Jensen [mailto:[EMAIL PROTECTED]] Sent: Sunday, July 15, 2001 17:50 To: Eyal Rif; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Slient install - Win32 Mysql MySQL doesn't create any Registry keys. The only registry entry that relates directly to MySQL (in addition to the one Monty's InstallShield sets so you can uninstall) is the one the Service Control Manager creates when you install the service. If you install MyODBC, you'll need to account for the keys your DSN's need (HKEY_LOCAL_MACHINE\Software\ODBC) Gerald Jensen - Original Message - From: Eyal Rif [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, July 15, 2001 10:06 AM Subject: Slient install - Win32 Mysql Hi, I am trying to create a Mysql Install packages with other Programs (Such as apache). Does any one know what registry keys need to be updated or how can I perform a silent install of win32-mysql ? Thanks, - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Deleting duplicate records
I'm new using mysql. I'm trying to load data from a text file to one of my table. This text file contains duplicate records. My question is how do you tell mysql to delete duplicate records and load just the ones that are not Why not just filter the data through uniq first? ~~ Ray B. Morris [EMAIL PROTECTED] http://www.webmastersguide.com Copyright 2002 Morris, All rights reserved - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 restart MySQL after innodb table filled up
Erik, this is probably not a bug. Your log files probably are not the ones which belong together with the current data file(s). When you start the database it tries to use the obsolete log files in recovery. From the manual: If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding .frm files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Erik Barker [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Monday, March 04, 2002 10:07 PM Subject: Can't restart MySQL after innodb table filled up I'm having problems restarting a MySQL server after a certain table reached maximum capacity. I managed to dump my entire database to a flat file before I restarted the server however when I tried to re-import my data the operation failed. My database size set to 500M in the innodb section of my my.cnf file. I'm running RH 7.2, kernel 2.4.19pre1 with 512M of RAM and have rebooted the server without any change in results. I've also upgraded MySQL-Max from 3.23.48 to 3.23.49a using the RPMS on mysql.com. I've tried removing all the innodb files within my database directory and MySQL seems to build the new 500M file properly but then crashes with the following message in the mysql.log: 020304 11:33:01 mysqld started 020304 11:33:02 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 1819350038 InnoDB: Assertion failure in thread 4096 in file ../include/buf0buf.ic line 265 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=268431360 record_buffer=1044480 sort_buffer=4194296 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 773739 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffe0b8, stack_bottom=0x49043190, thread_stack=65536, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x47f89f20 is invalid pointer thd-thread_id=138727664 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 138727664 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash 020304 11:33:02 mysqld ended - I've also tried changing a few values in the my.cnf file including changing the number of threads to 1 and increasing the size of the DB to 600M. Is this a known bug? Thanks, -- Erik Barker Sr. Systems Engineer NetNation Communications Inc. http://www.netnation.com | http://www.domainpeople.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 restart MySQL after innodb table filled up
On Monday 04 March 2002 03:21 pm, Heikki Tuuri wrote: Erik, this is probably not a bug. Your log files probably are not the ones which belong together with the current data file(s). When you start the database it tries to use the obsolete log files in recovery. From the manual: If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding .frm files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening. Is there now, or later, plans for an automatic procedure for deleting an InnoDB Db and ALL associated files (ie; Delete DB and all) ? Thanks, Anthony Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Erik Barker [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Monday, March 04, 2002 10:07 PM Subject: Can't restart MySQL after innodb table filled up I'm having problems restarting a MySQL server after a certain table reached maximum capacity. I managed to dump my entire database to a flat file before I restarted the server however when I tried to re-import my data the operation failed. My database size set to 500M in the innodb section of my my.cnf file. I'm running RH 7.2, kernel 2.4.19pre1 with 512M of RAM and have rebooted the server without any change in results. I've also upgraded MySQL-Max from 3.23.48 to 3.23.49a using the RPMS on mysql.com. I've tried removing all the innodb files within my database directory and MySQL seems to build the new 500M file properly but then crashes with the following message in the mysql.log: 020304 11:33:01 mysqld started 020304 11:33:02 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 1819350038 InnoDB: Assertion failure in thread 4096 in file ../include/buf0buf.ic line 265 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=268431360 record_buffer=1044480 sort_buffer=4194296 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 773739 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffe0b8, stack_bottom=0x49043190, thread_stack=65536, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x47f89f20 is invalid pointer thd-thread_id=138727664 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 138727664 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash 020304 11:33:02 mysqld ended - I've also tried changing a few values in the my.cnf file including changing the number of threads to 1 and increasing the size of the DB to 600M. Is this a known bug? Thanks, -- Erik Barker Sr. Systems Engineer NetNation Communications Inc. http://www.netnation.com | http://www.domainpeople.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
merge problems under 3.23.47?
Short version: I have 2 identical tables and I created a merge table to query them all at once (v 3.23.47). I can select on only two of the four keys in the table. Attempts to select data from the merge table WHERE column = xxx always return 0 rows for the other two keys. Queries using WHERE column LIKE xxx do work, but of course they're very, very slow. The problem seems to arise when the total line count of the two tables reaches 20. Long version: I have 2 tables that look like this (it's dhcp lease polling information): CREATE TABLE t1 ( ip_address_id INT UNSIGNED, cm_mac_id MEDIUMINT UNSIGNED, cpe_mac_id MEDIUMINT UNSIGNED, hostname_id INT UNSIGNED, starts DATETIME, endsDATETIME, KEY (ip_address_id), KEY (cm_mac_id), KEY (cpe_mac_id), UNIQUE KEY (ip_address_id, starts) ) TYPE=MyISAM RAID_TYPE=STRIPED RAID_CHUNKS=2 RAID_CHUNKSIZE=1024; I'm merging them together like this: CREATE TABLE t_merge ( ip_address_id INT UNSIGNED, cm_mac_id MEDIUMINT UNSIGNED, cpe_mac_id MEDIUMINT UNSIGNED, hostname_id INT UNSIGNED, starts DATETIME, endsDATETIME, KEY (ip_address_id), KEY (cm_mac_id), KEY (cpe_mac_id), KEY (ip_address_id, starts) ) TYPE=MERGE UNION=(t1, t2); These queries work fine: SELECT * FROM t_merge WHERE ip_address_id = 1234; SELECT * FROM t_merge WHERE ip_address_id = 1234 AND starts = '2002-03-02 00:59:05'; SELECT * FROM t_merge WHERE ip_address_id = 1234 AND cm_mac_id = 5678; SELECT * FROM t_merge WHERE ip_address_id = 1234 AND cpe_mac_id = 5678; DELETE FROM t_merge WHERE cm_mac_id = 5678; DELETE FROM t_merge WHERE cpe_mac_id = 5678; These queries return 0 rows: SELECT * FROM t_merge WHERE cm_mac_id = 5678; SELECT * FROM t_merge WHERE cpe_mac_id = 5678; These queries work correctly, but of course very slowly: SELECT * FROM t_merge WHERE cm_mac_id LIKE 1234; SELECT * FROM t_merge WHERE cpe_mac_id LIKE 1234; In testing, the problem only arises when the total line count of the two mapped tables reaches 20. Anyone have any ideas? - Matt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
difference between two times
Hello, I am trying to get an accurate (nearest 15 minutes would be fine) difference between two times that I have in my database. They are both of type time, and are in the format 00:00:00. For example, if I want the difference between 09:20:00 and 10:43:00, I would like it to give me 01:23:00 as the answer, but all I have been able to get it to do is give me one. It is rounding to the nearest hour. I found this query which seems like it would work, but gives me a NULL result set: SELECT DATE_SUB('11:48:02', INTERVAL '10:37:37' HOUR_SECOND); I am trying to do all of this from PHP, so if you happen to know of a way to make it do the subtraction in PHP without losing all accuracy, please let me know. Thanks for any help you can offer. Nathan Cowles StormNet Communications 530.897.4069 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Deleting rows from logging db efficiently
Using MySQL 3.23.40 App is logging db with 130 clients to a central MySQL db with 2 million events per day. Aged events of over 7 days are no longer required. The central table is myisam type with fixed size (datetime, enum and foreign keys only). Selects are done infrequently while inserts are done throughout the day. What is the fastest delete possible and still keep table optimized? Here are two ways that were developed. Either algorithm is done once a day when server activity is at a minimum. The first technique is in the documentation, the second technique is twice as fast. Delete rows and optimize DELETE FROM table WHERE etime2002-02-25; OPTIMIZE TABLE table; The time to delete 2 million rows is 24 minutes. The time to optimize is 18 minutes. Total time is 42 minutes. Transfer only newest data to no-index temporary table LOCK TABLES table t READ; INSERT INTO table_tmp SELECT * FROM table WHERE etime2002-02-25; TRUNCATE TABLE table; UNLOCK TABLES; INSERT INTO table SELECT * FROM table_tmp; The time to insert 10 million rows into temporary table is 3 minutes. The time to truncate table is 5 seconds. The time to insert from temporary table back to primary table is 18 minutes. Total time is 21 minutes. Does anyone know of a different approach of deleting rows while keeping the table optimized? Would welcome any comments. David PS1 Optimized table is defined as no deletes to table without a subsequent optimize. If deletes and inserts are done simultaneously, query times go up drastically. This slowdown is documented. PS2 Hardware is a 4 cpu Solaris with key_buffer=1024M thread_concurrency=8. Only other db in mysql is used infrequently. --- David E Lopez email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Deleting duplicate records
Thank you very much!! that was very helpful. By the way, is there any other way to get rid of duplicate records from a text file? Thanks again Nato -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Monday, March 04, 2002 3:00 PM To: Natividad Castro Cc: [EMAIL PROTECTED] Subject: Re: Deleting duplicate records On Mon, Mar 04, 2002 at 02:48:06PM -0500, Natividad Castro wrote: I'm new using mysql. I'm trying to load data from a text file to one of my table. This text file contains duplicate records. My question is how do you tell mysql to delete duplicate records and load just the ones that are not duplicate? I already tried the REPLACE and IGNORE key words but is I dont get the result that I want. Any idea If the data is in a text file, elimiate the duplicates before MySQL is even involved: cat myfile.txt | sort | uniq nodups.txt Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 25 days, processed 842,341,115 queries (386/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA LOCAL INFILE in Alpha 3.23.49]
I have the same problem using Linux RedHat 7.2. Just what do I have to configure my binary MySQL : Ver 8.23 Distrib 3.23.49a, for pc-linux-gnu on i686? The rest of MySQL works as advertised. Regards Tom Birchmire Michael Widenius [EMAIL PROTECTED] wrote: Hi! Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes: Sinisa [EMAIL PROTECTED] writes: Description: LOAD DATA LOCAL INFILE ... leads to 'The used command is not allowed with this MySQL version' This happens regardless of whether I have a local-infile = 1 in my /etc/my.cnf. In neither case does the local-infile variable show up with SHOW VARIABLES. We don't have a status variable that shows if this feature is enabled or not. The above is quite strange as I did test this properly before doing a release. Did you give the above option to both 'mysqld' and 'mysql' ? Which MySQL distribution are you using ? Sinisa Hi! Sinisa A new startup variable : Sinisa --local-infile Sinisa must be used to enable the above feature. That is what the user described that he did do... Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can't create a new thread
Hi! One of the users on a webserver I'm running are having some serious problems connecting to the MySQL server. He get's the following error: mysql error: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug. I've been reading the MySQL manual about some problems with old versions of glibc and RedHat 5.1, but this is a Debian testing-server running MySQL version 3.23.47, and glibc version 2.2.4. Any ideas on what I could do to prevent this? -- Trond Arve Nordheim - This message is ROT13-encrypted twice for extra security. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 bug
Hi! This is the test table: DROP TABLE IF EXISTS asinfo; CREATE TABLE asinfo ( asinfoID int(10) unsigned NOT NULL auto_increment, art char(1) binary NOT NULL default '', KNR char(5) NOT NULL default '', RECHNR char(6) NOT NULL default '', POSNR char(2) NOT NULL default '', ARTNR char(10) NOT NULL default '', TEX char(70) NOT NULL default '', PRIMARY KEY (asinfoID), KEY IdxArt (art), KEY IdxKnr (KNR), KEY IdxArtnr (ARTNR) ) TYPE=MyISAM; INSERT INTO asinfo VALUES (27,'j','','','','',''), [] # there are 620 'J' and 667 'j' records. select count(*) from asinfo where upper(art) = 'J'; select count(*) from asinfo where art = 'J' or art = 'j'; select count(*) from asinfo where art = 'j' or art = 'J'; mysql OS/2 3.23.42 count(*) 1287 count(*) 620 count(*) 667 mysql linux 3.23.44 count(*) 1287 count(*) 620 count(*) 667 So the Linux version is doing it the same way. For me it's a bug in both versions. Please reply. Thanks. tam -- Ducati 750SS '92 - on the road again http://tam.belchenstuermer.de/ Uptime 230d 22h 55m 04s - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: difference between two times
lastout and firstin contain times such as 13:28:01 and 13:27:54. If I use the query you gave me from within phpMyAdmin, the result I get is 7 seconds, exactly what it should be...but I do not know how to use PHP to get the correct result, it is giving me 23. I am accustomed to using mysql_result to get results but it needs to know the result variable ($result), the row (should be 0?), and then the last one is usually the field name, but in this case I don't think I have one because I'm asking it to select a math operation, so where does the result go, and how do I get it with PHP? Thanks. Nathan Cowles On Mon, 4 Mar 2002, Paul DuBois wrote: At 13:35 -0800 3/4/02, Nathan Cowles wrote: Paul, You're awesome...thank you. One more question pertaining to this... I'm accustomed to selecting a variable and using something like this: $firstin = mysql_result($result,$row,firstin); to set my variable. What do I want to do here to get the right result into my variable? $query2 = SELECT SEC_TO_TIME(TIME_TO_SEC('$lastout') - TIME_TO_SEC('$firstin')); $result2 = mysql_db_query($database, $query, $connection) or die (Error in query: $query. . mysql_error()); This isn't working, I get 23: $total = mysql_result($result2,0); I guess it depends on what the values of $firstin and $lastout are, and how you're displaying the value of $total. It's hard to say anything without seeing more of the code. Thanks again! Nathan Cowles On Mon, 4 Mar 2002, Paul DuBois wrote: Hello, I am trying to get an accurate (nearest 15 minutes would be fine) difference between two times that I have in my database. They are both of type time, and are in the format 00:00:00. For example, if I want the difference between 09:20:00 and 10:43:00, I would like it to give me 01:23:00 as the answer, but all I have been able to get it to do is give me one. It is rounding to the nearest hour. I found this query which seems like it would work, but gives me a NULL result set: SELECT DATE_SUB('11:48:02', INTERVAL '10:37:37' HOUR_SECOND); For two times t1 and t2, compute the difference in seconds like this: SELECT TIME_TO_SEC(t2) - TIME_TO_SEC(t1) If you want the result as a time value, pass it to SEC_TO_TIME(): SELECT SEC_TO_TIME(TIME_TO_SEC(t2) - TIME_TO_SEC(t1)) I am trying to do all of this from PHP, so if you happen to know of a way to make it do the subtraction in PHP without losing all accuracy, please let me know. Thanks for any help you can offer. Nathan Cowles StormNet Communications 530.897.4069 Nathan Cowles StormNet Communications 530.897.4069 Nathan Cowles StormNet Communications 530.897.4069 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php