Re: How to Run SQL Commands from a Text File stably?
Giuseppe Maxia wrote: 12/11/2001 05:32:28, PI Xu [EMAIL PROTECTED] wrote: Hi, Buddies: I used perl to run sql commands from a Text file, it always can't finish totally and report error in unstable lines . The error report that sql syntax error, but the syntax is same, just same, and many sql lines; Would you please tell me what this happen and how to resolve it? Thanks in Advance! Bill PERL COMMANDS: system('mysql -uusername -ppassword database pricfile.sql'); if pricfile.sql has 37871 lines sql commands like update products set retail=43.00 where partno='123456'; then the perl program give ERROR 1064 at line 37850: You have an error in your SQL syntax near ''99922' at line 1 I think you should have a look at the lines 37849 and 37850. The error could be that the previous one was wrongly terminated, or the current one has an invalid value. It could be a problem of unproper quoting. Having a look at the lines involved could be helpful. How did you produce the file? Bye Giuseppe if pricfile.sql has 1 lines, then the perl program give ERROR 1064 at line 19932: You have an error in your SQL syntax near '' at line 1 if pricfile.sql has 17872 lines sql commands like, then the perl program give ERROR 1064 at line 17848: You have an error in your SQL syntax near '' at line - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 One potential error is the lack of quoting in: retail=43.00 where... Try using: retail='43.00' where... Also, it would make things easier if you would give the exact error message, with the line where the error occurred. Denis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 mysql database from a servlet
I can't access mysql databases from servlets, using mmjdbc. When a servlet tries to execute: Connection conn = DriverManager.getConnection( jdbc:mysql://archive.infn.it/work?user=...password=...); it always gets this exception: java.sql.SQLException: Cannot connect to MySQL server on archive.infn.it:3306. Is there a MySQL server running on the machine/port you are trying to connect to? (java.security.AccessControlException)' Note that the same code executes without problems in an application (not a servlet) on the same machine, so it is not a problem of access privileges on the mysql server. Does somebody know the problem ? Luigi Fonti - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Cannot add blob data to innodb table
Hi Heikki, Comments inserted in text below: Steve, Date: Mon, 12 Nov 2001 08:52:54 To: [EMAIL PROTECTED] From: Heikki Tuuri [EMAIL PROTECTED] Subject: Re: Cannot add blob data to innodb table Stephen, Hi, I have an Access97 table containing binary fields (gifs) that I would like to migrate to an innodb format via MyODBC (2.50.39) and MySQL-Max (3.23.44). The MySQL query log shows binary-like characters being received but there never seems to be progression to the next record. After 5 or so minutes, the append query in Access97 quits and an ODBC error window complaining about a lost connection pops-up. When I convert the table to myisam type, the append query works. Is there something in my.cnf I need to adjust to fix thisproblem? inserting binary BLOBs should work. What is an 'append' query in MS Access? Is it translated to an INSERT in MySQL? Could you paste a copy of what the MySQL query logs shows? The create statement from the query log is: CREATE TABLE tbl_boards(BID INT NOT NULL,_Group INT NULL,Code CHAR(10) NULL,Description CHAR(50) NULL,Board_Size CHAR(50) NULL,Product_Line CHAR(30) NULL,Featuring CHAR(255) NULL,Colour CHAR(255) NULL,Picture LONGBLOB NULL,Label LONGBLOB NULL, PRIMARY KEY (BID)); and the INSERT statement is: INSERT INTO `tbl_boards` (`BID`,`_Group`,`Code`,`Description`,`Board_Size`,`Product_Line`,`Picture`,`Label`)VALUES (1,1,'1','Colour Board - Glasstyle','450mm x 450mm x 6mm','Glasstyle','^U^\3\0^B\0\0\0^S\0^L\0^T\0\'\0Photo Editor Photo\0MSPhotoEd.3\0^A^E\0\0^B\0\ etc. How big is the BLOB? What is the CREATE TABLE statement? Note that a BLOB bigger than 64 kB needs to be defined a MEDIUMBLOB or LONGBLOB in the CREATE TABLE statement. The blobs are about 250-400K each, the average row length is 850K and the max_allowed_packet is 1M. It could be that some of the records are 1M. I will check that. However, as I mentioned before, the INSERT works with myisam tables. If you try inserting an ASCII text file does that work? How big you have set max_allowed_packet in my.cnf? That restricts the size of rows communicated from a client to the server. Thanks, Stephen Regards, Heikki http://www.innodb.com actually, are you running MySQL on Windows? Then the problem might be the bug introduced in 3.23.42: to access InnoDB tables you must use the same case of letters in the database name as you used in the CREATE TABLE statement. Make sure you consistently use lower case in database names. The bug is fixed in upcoming 3.23.45. Does mysql.err contain anything? I am running mysql-max on Linux (RedHat7.1) and mysql.err does not have any error-related messages. Regards, Heikki Thanks, Stephen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Cannot add blob data to innodb table
Stephen, At 12:53 AM 11/12/01 -0800, you wrote: Hi Heikki, Comments inserted in text below: Steve, Date: Mon, 12 Nov 2001 08:52:54 To: [EMAIL PROTECTED] From: Heikki Tuuri [EMAIL PROTECTED] Subject: Re: Cannot add blob data to innodb table Stephen, Hi, I have an Access97 table containing binary fields (gifs) that I would like to migrate to an innodb format via MyODBC (2.50.39) and MySQL-Max (3.23.44). The MySQL query log shows binary-like characters being received but there never seems to be progression to the next record. After 5 or so minutes, the append query in Access97 quits and an ODBC error window complaining about a lost connection pops-up. When I convert the table to myisam type, the append query works. Is there something in my.cnf I need to adjust to fix thisproblem? inserting binary BLOBs should work. What is an 'append' query in MS Access? Is it translated to an INSERT in MySQL? Could you paste a copy of what the MySQL query logs shows? The create statement from the query log is: CREATE TABLE tbl_boards(BID INT NOT NULL,_Group INT NULL,Code CHAR(10) NULL,Description CHAR(50) NULL,Board_Size CHAR(50) NULL,Product_Line CHAR(30) NULL,Featuring CHAR(255) NULL,Colour CHAR(255) NULL,Picture LONGBLOB NULL,Label LONGBLOB NULL, PRIMARY KEY (BID)); and the INSERT statement is: INSERT INTO `tbl_boards` (`BID`,`_Group`,`Code`,`Description`,`Board_Size`,`Product_Line`,`Picture`, `Label`)VALUES (1,1,'1','Colour Board - Glasstyle','450mm x 450mm x 6mm','Glasstyle','^U^\3\0^B\0\0\0^S\0^L\0^T\0\'\0Photo Editor Photo\0MSPhotoEd.3\0^A^E\0\0^B\0\ etc. How big is the BLOB? What is the CREATE TABLE statement? Note that a BLOB bigger than 64 kB needs to be defined a MEDIUMBLOB or LONGBLOB in the CREATE TABLE statement. The blobs are about 250-400K each, the average row length is 850K and the max_allowed_packet is 1M. It could be that some of the records are 1M. I will check that. However, as I mentioned before, the INSERT works with myisam tables. If you try inserting an ASCII text file does that work? How big you have set max_allowed_packet in my.cnf? That restricts the size of rows communicated from a client to the server. Thanks, Stephen Regards, Heikki http://www.innodb.com actually, are you running MySQL on Windows? Then the problem might be the bug introduced in 3.23.42: to access InnoDB tables you must use the same case of letters in the database name as you used in the CREATE TABLE statement. Make sure you consistently use lower case in database names. The bug is fixed in upcoming 3.23.45. Does mysql.err contain anything? I am running mysql-max on Linux (RedHat7.1) and mysql.err does not have any error-related messages. Regards, Heikki Thanks, Stephen how do you communicate the binary strings to MySQL? From the manual I found: If you want to insert binary data into a BLOB column, the following characters must be represented by escape sequences: NUL ASCII 0. You should represent this by `\0' (a backslash and an ASCII `0' character). \ ASCII 92, backslash. Represent this by `\\'. ' ASCII 39, single quote. Represent this by `\''. ASCII 34, double quote. Represent this by `\'. If you write C code, you can use the C API function mysql_escape_string() to escape characters for the INSERT statement. See section 8.4.2 C API Function Overview. In Perl, you can use the quote method of the DBI package to convert special characters to the proper escape sequences. See section 8.2.2 The DBI Interface. You should use an escape function on any string that might contain any of the special characters listed above! Try also inserting rows to your table from the mysql command line client. Does that work? Does MyODBC work if you insert simple ASCII strings (not binary strings) in place of the BLOBs: 'jhghjghgjgjgjkgkjhhj'. Regards, Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Accessing mysql database from a servlet
i think you should check the privileges : grant all on work.* to user@host identified by pass check again the host, because host in servlet seem does not like in another application. i'm working on my own computer, MySQL query from PHP is my domain, but from servlet is localhost :) u should test: try { yr sql query } catch (Exception e) { // not SQLException out.println(e.getMessage()); } to see what message from Exception. - Original Message - From: Luigi Fonti [EMAIL PROTECTED] To: Nguyen Trong Phuc [EMAIL PROTECTED] Sent: Monday, November 12, 2001 1:05 AM Subject: Re: Accessing mysql database from a servlet It is exactly what I do. But in a normal java application it works, while in a servlet it doesn't. Luigi Fonti Nguyen Trong Phuc wrote: this is the way to connect to MySQL from JDBC: Class.forName(driverName).newInstance(); Connection con = DriverManager.getConnection(dbURL, userName, passWord); Statement stmt = con.createStatement(); driverName = org.gjt.mm.mysql.Driver dbURL = jdbc:mysql://host:3306/db - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: pass variable in JS to php and mysql-query
I'm working in an application using JS with ASP and MSQL. We use Remote Scripting for real-time updating and reading the Database without reloading a page or move to another page. It works very well. More details on this one can be found at http://msdn.microsoft.com/scripting/ I guess it isn't very hard to make such a thing (and even simpler then MS' one) for PHP. I hope so, 'cause I like to use it with my own PHP projects too. Greetinx Michaël Hompus i think that u can't, b/c php is server side while JS is client site. u can doSubmit() when building is changed, then php script get infomation and response to browser. Trong Phuc script language=javascript function changebuilding (which) { sel_var = which.selectedIndex ? $sql_query = select distinct room from general.room_info where building='$variable' group by level asc; $sql_result = mysql_query($sql_query) or die ( Can't execute the query); $i=0; while ($row = mysql_fetch_array($result_sec_level)) print (ar[.$i++.] = \.$row[0].\;\n); ? for (i=0; i ar.length; i++) { option = new Option(ar[i]) document.myform.room.options[i]=option } } /script body form name=myform Building : select name=building onchange=changebuilding(this) option1/option option2/option option3/option /select Room : select name=room /select /form /body I can pass the variable in building to $variable by submit button but I want to pass the sel_var variable to $variable and dynamic update the content in room. -- phil Huynh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Adding LoginName and Passwords programmatically
Hello, I have a database of about 40K members, which I want to port to MySQL. This is not the problem, my problem is that I have the following table: CREATE TABLE Person ( PersonID int(11) NOT NULL auto_increment, Title varchar(10) default NULL, FirstName varchar(35) NOT NULL default '', Initials varchar(5) default NULL, Surname varchar(35) NOT NULL default '', Email varchar(45) default NULL, LoginName varchar(10) NOT NULL default '', Password varchar(10) default NULL, PreviousSource varchar(10) default NULL, RegDate timestamp(14) NOT NULL, FK_BusID int(11) NOT NULL default '0', FK_GroupsID int(11) NOT NULL default '0', FK_JobFunID int(11) NOT NULL default '0', FK_SourceID int(11) NOT NULL default '0', FK_StatusID int(11) NOT NULL default '0', FK_PurchaseID int(11) NOT NULL default '0', JobTitle varchar(40) default NULL, Tel varchar(20) default NULL, Fax varchar(20) default NULL, PRIMARY KEY (PersonID), KEY IDX_Person (LoginName,Surname) ) TYPE=ISAM; I have all the data, but I would like to add the LoginName and Password during the import of the raw data so that each of these are unique and randomly generated. I have a form which does this, in php, but I can only add one record at a time, is there a way in which I can ask the script to look up the import file, load the data onto the database and for each record generate a LoginName and Password. Here is the part of my php form that generates the LoginName Password for individual entries. // generate login name from fname, lname, rand // seed with microseconds since last whole second srand ((double) microtime() * 100); $randval = rand(); $reglogin = substr($fname,0,2) . substr($lname,0,2) . $randval; $reglogin = substr($reglogin, 0, 10); $passwd = generateRandomPassword(); Your advice is much appreciated. Many thanks Norman - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: C API mysql_query() malfunctioning (long)
So you mean to replace joining with combined condition? I think it is simply a terminological/syntactical difference. I simply never use LEFT JOIN commands. I always use '='. I think they do the same think, namely what is called a join in relational _theory_. I *understand* '=' better then JOIN. * * * It is good we found your bug now! There is still the question of your mysql monitor seemingly yelding the right results for the wrong query, but I'll leave that problem for you now ;-) -- , M A R I O data miner, LIACC, room 221 tel 351+226078830, ext 121 A M A D O Rua Campo Alegre, 823 fax 351+226003654 A L V E S P-4150 PORTO, Portugalmob 351+939354002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Any possibility for userstamp column type?
It occurred to me that a column type that functioned similar to timestamp but for the id of user initiating the update would be very useful. What I mean is a column that would automatically capture the userid that last changed a row, much as a first timestamp column is automatically updated to reflect the time a row was last changed. Is such a column type possible in MySQL and are there any plans to implement it? Thanks, Rich - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using MySQL++ with CodeWarrior 7
Brice Ruth writes: Greetings list members. I'm trying to build an application for OS X using CW7 - and though I've gotten mysql++ to build and install properly (from the command line, using configure gcc) - I can't seem to get CW happy with using mysql++ ... when I include the sqlplus header, I get about 300+ errors (and numerous warnings) about things that the CW compiler apparently doesn't care for. Does anyone know what might be going on? I'm not really interested in rewriting copious amounts of mysql++ to make it compatible - since I don't consider my C++ experience to be quite up to that level! Any help would be appreciated! Sincerely, Brice Ruth p.s. please cc: me directly, as I'm not currently on the list. Also - apologies if this isn't the correct place to post, please let me know if it isn't. -- WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/ Hi! I have not tested MySQL++ with CodeWarrior, but I heard that it could be feasible. Are you sure that you can build MySQL++ with GNU and use it with CW ?? I am not so sure. I think you will have to port MySQL++ to CW Take a look at other ports, like Compaq's, Borland's, VC++'s You will simply have to resolve all the errors that you have. I have never used CW, nor do I have Mac (;-() so I can be of some theoretical help to you only. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to auto_decrement a primary key?
Hi all, i want to keep the track of row numbers to display the results , say, five by five. the most common way to do this is defining a column which is auto_incrementing and not null. but htere is a problem with this approach. say, i have a table which stores the subject and contents of articles, and it has a primary key, say pk. since pk is auto incrementing , whenever a new article is added it will increment by one.However, if i delete an article ( a row) it will not decrement. So when i want to display them five by five, i will have trouble, because some numbers do not exist. In oracle there is an imbedded variable called rownum, so that you can workaround this. Is there a similar varible or a kind of solution for this problem? i am looking forward to your help, and suggestions. many thanks in advance cheers :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: how to auto_decrement a primary key?
On 12-Nov-2001 yilmaz wrote: Hi all, i want to keep the track of row numbers to display the results , say, five by five. the most common way to do this is defining a column which is auto_incrementing and not null. but htere is a problem with this approach. say, i have a table which stores the subject and contents of articles, and it has a primary key, say pk. since pk is auto incrementing , whenever a new article is added it will increment by one.However, if i delete an article ( a row) it will not decrement. So when i want to display them five by five, i will have trouble, because some numbers do not exist. In oracle there is an imbedded variable called rownum, so that you can workaround this. Is there a similar varible or a kind of solution for this problem? i am looking forward to your help, and suggestions. many thanks in advance look for LIMIT in the manual. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL and Physical strorage
Hello my name is Sebastien and i am french student in database administration. I'm searching for vues or tables in MySQL like dba_ or dbms_ in Oracle db soft. How are stored data in the files etc... Are they tablespaces, segments, extents or blocs. If exists, what is the structure of these things. Sorry for my poor english Thanx for an answer. [EMAIL PROTECTED] SEB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
currency: which data type?
Hi, I'm interested to know what data type people consider suitable for storing currency. In a table in my database for a university assignment, i have: create table Products( ... price double(4,2) not null, ...); However it has been suggested to me that double(4,2) is inappropriate and that decimal or numeric would be more appropriate. I only used double(4,2) because this is what is used in the MySQL tutorial (I think it's chapter 3 of the manual). Any thoughts on this matter appreciated. Regards, Saqib Shaikh Email: [EMAIL PROTECTED] Web site: http://www.saqibshaikh.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: problem to compile mysql on HPux 11
Hi I want to install MySql on my HP server. I take the source and : I have made a ./configure then # make No suffix list. make all-recursive No suffix list. Making all in include No suffix list. Making all in Docs Making all in readline gcc -DUNDEF_THREADS_HACK -DHAVE_CONFIG_H -I./../include -I./.. -I.. -D__STD C_EXT__ -O3 -DDBUG_OFF * DHAVE_BROKEN_PREAD -DDONT_USE_FINITE -DHAVE_BROKEN_GETPASS -DNO_FCNTL_NONBL OCK -c rltty.c command line: warning: __STDC_EXT__ redefined command line: warning: this is the location of the previous definition rltty.c: In function 'set_tty_settings': rltty.c:231: 'TIOCSETN' undeclared (first use in this function) rltty.c:231: (Each undeclared identifier is reported only once rltty.c:231: for each function it appears in.) rltty.c: In function 'prepare_terminal_settings': rltty.c:286: 'CBREAK' undeclared (first use in this function) *** Error exit code 1 Stop. *** Error exit code 1 Stop. *** Error exit code 1 Stop. Can you help me ? I don't understand if something is missing. Regards, - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
multiple cpus
Hello, Will a single instance of MySQL use multiple CPU's? I didn't see anything about it in the docs... Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
auto_increment counter changes even if query fails
Hi, Is this correct for MySQL to increment the auto_increment counter if the INSERT query fails? For example: mysql CREATE TABLE test ( i INT NOT NULL AUTO_INCREMENT, c CHAR(16) NOT NULL, PRIMARY KEY(i), UNIQUE(c)) type=InnoDB; Query OK, 0 rows affected (0.25 sec) mysql INSERT INTO test VALUES('', 'test1'); Query OK, 1 row affected (0.05 sec) mysql INSERT INTO test VALUES('', 'test2'); Query OK, 1 row affected (0.24 sec) mysql INSERT INTO test VALUES('', 'test2'); ERROR 1062: Duplicate entry 'test2' for key 2 mysql INSERT INTO test VALUES('', 'test3'); Query OK, 1 row affected (0.02 sec) mysql SELECT * FROM test; +---+---+ | i | c | +---+---+ | 1 | test1 | | 2 | test2 | | 4 | test3 | +---+---+ 3 rows in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FROM clause order matters?
[EMAIL PROTECTED] writes: Description: We recently tried to upgrade from mysql 3.23.27 to 3.23.43 - we switched back caused by we sometimes recieved different results with 3.23.43. The statements below gives the same result with 3.23.27 but not with 3.23.43. Apperently it matters in what order the tables are written in the FROM clause. I have tried to narrow it down to two simple tables, but then the problem disappeared. How-To-Repeat: mysql SELECT link.titel,link.id FROM link, ord as o1,ord AS o2 WHERE o1.ord like 'bethanias%' AND o2.ord like 'unge%' AND o1.kilde_id=o2.kilde_id AND o1.kilde_id=link.id; ++-+ | titel | id | ++-+ | Bethanias Unge | 404 | ++-+ 1 row in set (0.01 sec) mysql SELECT link.titel,link.id FROM ord as o1,ord AS o2, link WHERE o1.ord like 'bethanias%' AND o2.ord like 'unge%' AND o1.kilde_id=o2.kilde_id AND o1.kilde_id=link.id; Empty set (0.01 sec) mysql explain SELECT link.titel,link.id FROM link, ord as o1,ord AS o2 WHERE o1.ord like 'bethanias%' AND o2.ord like 'unge%' AND o1.kilde_id=o2.kilde_id AND o1.kilde_id=link.id; +---++---+-+-+-+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+-+--++ | o1| range | iord,kilde_id,ord | iord| 61 | NULL|1 | where used | | link | eq_ref | PRIMARY | PRIMARY | 4 | o1.kilde_id |1 | | | o2| range | iord,kilde_id,ord | iord| 61 | NULL| 121 | where used | +---++---+-+-+-+--++ 3 rows in set (0.00 sec) mysql explain SELECT link.titel,link.id FROM ord as o1,ord AS o2, link WHERE o1.ord like 'bethanias%' AND o2.ord like 'unge%' AND o1.kilde_id=o2.kilde_id AND o1.kilde_id=link.id; +---++---+-+-+-+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+-+--++ | o1| range | iord,kilde_id,ord | iord| 61 | NULL|1 | where used | | o2| range | iord,kilde_id,ord | iord| 61 | NULL| 121 | where used | | link | eq_ref | PRIMARY | PRIMARY | 4 | o1.kilde_id |1 | | +---++---+-+-+-+--++ 3 rows in set (0.00 sec) mysql Fix: Hi! Can you tar and gzip your tables and upload them to : ftp://support.mysql.com:/pub/mysql/secret so that we can check it out ?? After you upload them, please let us know a filename. -- 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: Problem: myisamchk: error: Checksum for key....
Grzegorz Paszka writes: Hi. I've problem with mysql. I had i386 RH71 (kernel 2.4.13, 1.5GB RAM, single procesor) with mysql 3.23.36 (now I have 3.23.41 and problem still exists). I run myisamchk on table zawartosc: [root@tygrys rpm]# myisamchk -c -i -v -w zawartosc Checking MyISAM file: zawartosc Data records: 6995929 Deleted blocks: 0 - check file-size - check key delete-chain block_size 1024: - check record delete-chain No recordlinks - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 98% Packed:0% Max levels: 4 - check data record references index: 2 Key: 2: Keyblocks used: 65% Packed: 60% Max levels: 7 Total:Keyblocks used: 69% Packed: 56% - check record links myisamchk: error: Checksum for key: 2 doesn't match checksum for records Record blocks: 6995929Delete blocks: 0 Record data: 398291567Deleted data: 0 Lost space:5259802Linkdata: 26232723 MyISAM-table 'zawartosc' is corrupted Fix it using switch -r or -o Of course I used switch -r and problem still exists. Second I used switch -o and problem still exists. Hi! I hope you have not run myisamchk while server was running If yes, then that would explain things. Try using CHECK TABLE SQL command instead. Do you want to say that there is no way that you can repair MyISAM table ?? In that case there is in our fine manual a description of how to solve most critical table corruption. This basically involves rebuilding of entire index file. -- 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
MySQL Developer's Handbook
Jeremy, I was just cruising amazon.com for MYSQL books and I noted you have a MYSQL book due-out in January. Will it cover version 4.0? Since mySQL vers 4.1 is due out soon, do you intend to await the book's release to cover the additional functionality, such as sub-SELECTS? What dose the Table of Contents look like? Will you post a chapter on the internet somewhere to give potential readers insight into contents and style? I ask that, because I noted that Paul DuBois has done this for his book. 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
Re: updating cached query results as database changes?
Bennett Haselton writes: I don't know if MySQL handles this, but if a database system knows about a particular type of query that is executed very often (either you enter this query manually as a common query, or the database detects it), can you instruct the DBMS to cache the results of the query -- and to examine all updates to the data, so if an update causes the query results to change, the cached results will be changed accordingly? Is there a name for this kind of practice, some word that I could look up in the index of a book about databases? [skip] -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 Hi! Query cacheing is right now in the works in 4.0. -- 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: auto_increment counter changes even if query fails
Maciek, Hi, Is this correct for MySQL to increment the auto_increment counter if the INSERT query fails? For example: mysql CREATE TABLE test ( i INT NOT NULL AUTO_INCREMENT, c CHAR(16) NOT NULL, PRIMARY KEY(i), UNIQUE(c)) type=InnoDB; Query OK, 0 rows affected (0.25 sec) mysql INSERT INTO test VALUES('', 'test1'); Query OK, 1 row affected (0.05 sec) mysql INSERT INTO test VALUES('', 'test2'); Query OK, 1 row affected (0.24 sec) mysql INSERT INTO test VALUES('', 'test2'); ERROR 1062: Duplicate entry 'test2' for key 2 mysql INSERT INTO test VALUES('', 'test3'); Query OK, 1 row affected (0.02 sec) mysql SELECT * FROM test; +---+---+ | i | c | +---+---+ | 1 | test1 | | 2 | test2 | | 4 | test3 | +---+---+ 3 rows in set (0.00 sec) auto-inc values are assigned past the transactional mechanism, for efficiency, and to avoid deadlocks. Therefore a rollback following a failing insert statement does not roll back the counter. If you want a strictly contiguous sequence of values, use the standard technique of a counter row in another table, from which you assign the values to your insert statements. CREATE TABLE test_counter(a INT) TYPE = INNODB; SELECT a FROM testcounter FOR UPDATE; UPDATE testcounter SET a = a + 1; INSERT INTO test (...); Regards, Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Compiling of Mysql++ 1.7.9 under RH7.1
Rasmus Theede writes: When I try to compile any of the examples in mysql++ I get the errors (RedHat 7.1): In file included from /usr/local/include/sqlplus.hh:9, from simple1.cc:3: /usr/local/include/defs:5:19: mysql.h: No such file or directory In file included from /usr/local/include/coldata1.hh:8, from /usr/local/include/sqlplus.hh:12, from simple1.cc:3: /usr/local/include/type_info1.hh:10:19: mysql.h: No such file or directory In file included from /usr/local/include/vallist1.hh:6, from /usr/local/include/row1.hh:11, from /usr/local/include/compare1.hh:5, from /usr/local/include/sqlplus.hh:13, from simple1.cc:3: /usr/local/include/manip1.hh:10:19: mysql.h: No such file or directory In file included from /usr/local/include/sqlplus.hh:14, from simple1.cc:3: /usr/local/include/connection1.hh:10:19: mysql.h: No such file or directory In file included from /usr/local/include/connection1.hh:20, from /usr/local/include/sqlplus.hh:14, from simple1.cc:3: /usr/local/include/query1.hh:10:19: mysql.h: No such file or directory In file included from /usr/local/include/query1.hh:13, from /usr/local/include/connection1.hh:20, from /usr/local/include/sqlplus.hh:14, from simple1.cc:3: /usr/local/include/result1.hh:12:19: mysql.h: No such file or directory In file included from /usr/local/include/sqlplus.hh:57, from simple1.cc:3: /usr/local/include/coldata3.hh:10:19: mysql.h: No such file or directory Where do I go wrong?? Regards Hi! You have to provide a full path to C API includes and libs, as explained in our manual. You will find proper options for path names to be passed to configure. Although MySQL++ 1.7.9 works with latest 2.96 compilers, we would still recommend any from 2.95 or 3.* series ... -- 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: auto_increment counter changes even if query fails
Maciek, Hi, Is this correct for MySQL to increment the auto_increment counter if the INSERT query fails? For example: mysql CREATE TABLE test ( i INT NOT NULL AUTO_INCREMENT, c CHAR(16) NOT NULL, PRIMARY KEY(i), UNIQUE(c)) type=InnoDB; Query OK, 0 rows affected (0.25 sec) mysql INSERT INTO test VALUES('', 'test1'); Query OK, 1 row affected (0.05 sec) mysql INSERT INTO test VALUES('', 'test2'); Query OK, 1 row affected (0.24 sec) mysql INSERT INTO test VALUES('', 'test2'); ERROR 1062: Duplicate entry 'test2' for key 2 mysql INSERT INTO test VALUES('', 'test3'); Query OK, 1 row affected (0.02 sec) mysql SELECT * FROM test; +---+---+ | i | c | +---+---+ | 1 | test1 | | 2 | test2 | | 4 | test3 | +---+---+ 3 rows in set (0.00 sec) auto-inc values are assigned past the transactional mechanism, for efficiency, and to avoid deadlocks. Therefore a rollback following a failing insert statement does not roll back the counter. If you want a strictly contiguous sequence of values, use the standard technique of a counter row in another table, from which you assign the values to your insert statements. CREATE TABLE test_counter(a INT) TYPE = INNODB; SELECT a FROM testcounter FOR UPDATE; UPDATE testcounter SET a = a + 1; INSERT INTO test (...); Regards, Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment counter changes even if query fails
Maciek, Hi, Is this correct for MySQL to increment the auto_increment counter if the INSERT query fails? For example: mysql CREATE TABLE test ( i INT NOT NULL AUTO_INCREMENT, c CHAR(16) NOT NULL, PRIMARY KEY(i), UNIQUE(c)) type=InnoDB; Query OK, 0 rows affected (0.25 sec) mysql INSERT INTO test VALUES('', 'test1'); Query OK, 1 row affected (0.05 sec) mysql INSERT INTO test VALUES('', 'test2'); Query OK, 1 row affected (0.24 sec) mysql INSERT INTO test VALUES('', 'test2'); ERROR 1062: Duplicate entry 'test2' for key 2 mysql INSERT INTO test VALUES('', 'test3'); Query OK, 1 row affected (0.02 sec) mysql SELECT * FROM test; +---+---+ | i | c | +---+---+ | 1 | test1 | | 2 | test2 | | 4 | test3 | +---+---+ 3 rows in set (0.00 sec) auto-inc values are assigned past the transactional mechanism, for efficiency, and to avoid deadlocks. Therefore a rollback following a failing insert statement does not roll back the counter. If you want a strictly contiguous sequence of values, use the standard technique of a counter row in another table, from which you assign the values to your insert statements. CREATE TABLE test_counter(a INT) TYPE = INNODB; SELECT a FROM testcounter FOR UPDATE; UPDATE testcounter SET a = a + 1; INSERT INTO test (...); Regards, Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment counter changes even if query fails
Maciek, Hi, Is this correct for MySQL to increment the auto_increment counter if the INSERT query fails? For example: mysql CREATE TABLE test ( i INT NOT NULL AUTO_INCREMENT, c CHAR(16) NOT NULL, PRIMARY KEY(i), UNIQUE(c)) type=InnoDB; Query OK, 0 rows affected (0.25 sec) mysql INSERT INTO test VALUES('', 'test1'); Query OK, 1 row affected (0.05 sec) mysql INSERT INTO test VALUES('', 'test2'); Query OK, 1 row affected (0.24 sec) mysql INSERT INTO test VALUES('', 'test2'); ERROR 1062: Duplicate entry 'test2' for key 2 mysql INSERT INTO test VALUES('', 'test3'); Query OK, 1 row affected (0.02 sec) mysql SELECT * FROM test; +---+---+ | i | c | +---+---+ | 1 | test1 | | 2 | test2 | | 4 | test3 | +---+---+ 3 rows in set (0.00 sec) auto-inc values are assigned past the transactional mechanism, for efficiency, and to avoid deadlocks. Therefore a rollback following a failing insert statement does not roll back the counter. If you want a strictly contiguous sequence of values, use the standard technique of a counter row in another table, from which you assign the values to your insert statements. CREATE TABLE test_counter(a INT) TYPE = INNODB; SELECT a FROM testcounter FOR UPDATE; UPDATE testcounter SET a = a + 1; INSERT INTO test (...); Regards, Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment counter changes even if query fails
Maciek, Hi, Is this correct for MySQL to increment the auto_increment counter if the INSERT query fails? For example: mysql CREATE TABLE test ( i INT NOT NULL AUTO_INCREMENT, c CHAR(16) NOT NULL, PRIMARY KEY(i), UNIQUE(c)) type=InnoDB; Query OK, 0 rows affected (0.25 sec) mysql INSERT INTO test VALUES('', 'test1'); Query OK, 1 row affected (0.05 sec) mysql INSERT INTO test VALUES('', 'test2'); Query OK, 1 row affected (0.24 sec) mysql INSERT INTO test VALUES('', 'test2'); ERROR 1062: Duplicate entry 'test2' for key 2 mysql INSERT INTO test VALUES('', 'test3'); Query OK, 1 row affected (0.02 sec) mysql SELECT * FROM test; +---+---+ | i | c | +---+---+ | 1 | test1 | | 2 | test2 | | 4 | test3 | +---+---+ 3 rows in set (0.00 sec) auto-inc values are assigned past the transactional mechanism, for efficiency, and to avoid deadlocks. Therefore a rollback following a failing insert statement does not roll back the counter. If you want a strictly contiguous sequence of values, use the standard technique of a counter row in another table, from which you assign the values to your insert statements. CREATE TABLE test_counter(a INT) TYPE = INNODB; SELECT a FROM testcounter FOR UPDATE; UPDATE testcounter SET a = a + 1; INSERT INTO test (...); Regards, Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment counter changes even if query fails
Maciek, Hi, Is this correct for MySQL to increment the auto_increment counter if the INSERT query fails? For example: mysql CREATE TABLE test ( i INT NOT NULL AUTO_INCREMENT, c CHAR(16) NOT NULL, PRIMARY KEY(i), UNIQUE(c)) type=InnoDB; Query OK, 0 rows affected (0.25 sec) mysql INSERT INTO test VALUES('', 'test1'); Query OK, 1 row affected (0.05 sec) mysql INSERT INTO test VALUES('', 'test2'); Query OK, 1 row affected (0.24 sec) mysql INSERT INTO test VALUES('', 'test2'); ERROR 1062: Duplicate entry 'test2' for key 2 mysql INSERT INTO test VALUES('', 'test3'); Query OK, 1 row affected (0.02 sec) mysql SELECT * FROM test; +---+---+ | i | c | +---+---+ | 1 | test1 | | 2 | test2 | | 4 | test3 | +---+---+ 3 rows in set (0.00 sec) auto-inc values are assigned past the transactional mechanism, for efficiency, and to avoid deadlocks. Therefore a rollback following a failing insert statement does not roll back the counter. If you want a strictly contiguous sequence of values, use the standard technique of a counter row in another table, from which you assign the values to your insert statements. CREATE TABLE test_counter(a INT) TYPE = INNODB; SELECT a FROM testcounter FOR UPDATE; UPDATE testcounter SET a = a + 1; INSERT INTO test (...); Regards, Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Any possibility for userstamp column type?
Rich Bartell writes: It occurred to me that a column type that functioned similar to timestamp but for the id of user initiating the update would be very useful. What I mean is a column that would automatically capture the userid that last changed a row, much as a first timestamp column is automatically updated to reflect the time a row was last changed. Is such a column type possible in MySQL and are there any plans to implement it? Thanks, Rich No, no plans. This feature is very easy to implement in the application program and there are not many users that have asked for 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: Any possibility for userstamp column type?
Sinisa Milivojevic writes: Rich Bartell writes: It occurred to me that a column type that functioned similar to timestamp but for the id of user initiating the update would be very useful. No, no plans. This feature is very easy to implement in the application program and there are not many users that have asked for it. This does not surprise me. The MySQL privilege system is generally not useful as a substitute for application-level user handling, just as the system's user handling (/etc/passwd etc.) would not be useful for MySQL. I can see how it might be useful to have a few different mysql users that one application switches between based on what application-level user is using the application, but that's about it. //C - expressing his $0.02 worth of thoughts. -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: lost in the Land of Oz Parking Lot
Hi Jason On Friday 09 November 2001 00:26, [EMAIL PROTECTED] wrote: I am lost. I am a very VERY green newbie, and I am struggling on just how to begin. Basically, I have bought a book, entitled, PHP and MYSQL Web Development . , I have downloaded mysql-3.23.43.tar.gz, the install for windows document (yes I have not even made it passed the Install! Oh I am PATHETIC!) says to use Setup.exe for windows. The problem is I can not find that file anywhere. This is a very good book indeed. But since you confess to being an absolute beginner (aren't we all?), I wonder whether I might point you in the direction of a book specifically for beginners. It is PHP Fast and Easy, by Julie Meloni, pubished by PrimaTech. It comes with a CD with all the stuff on it (although probably a bit dated by now - it's been out for a year), and detailed install instructions (everything went on first-time on Win95, once I'd updated the winsock to v2, so Win98 should have no problems at all). Only about 10 or 11 different types of page are covered, but they each have step-by-step walkthroughs, and I was able to read the whole book through without getting lost. Once you've done that, you'll have a good basis for going on to Welling and Thomson. HTH Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
lcc-win32 couldn't compile..........
Hi I'm new mySQL user (on Windows). I tried to compile run the example C code myTest.c using lcc-win32 IDE but it throws errors. Although I put proper header path at setting...I think there is some problem with linker. I also included the libmysql.lib file but in vain.. When the same code is compiled linked using MS-VC++, it runs...Could anybody guide me how to tune lcc-win32 or any other C IDE..I don't want to use MS-VC++... Ali __ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to Run SQL Commands from a Text File stably?
I used perl to run sql commands from a Text file, it always can't finish totally and report error in unstable lines . The error report that sql syntax error, but the syntax is same, just same, and many sql lines; Would you please tell me what this happen and how to resolve it? What you ought to do is use perl to parse the file and run the commands individually. use DBI; my $driver=mysql; my $database=database; my $user=username; my $password=password; my $dns = DBI:$driver:database=$database; my $db=DBI-connect($dns, $user, $password); open SQL, $ARGV[0]; open ERRORS, errors.sql; while (my $line = SQL) { $line =~ s/;$// ; $db-do($line); if $db-err { write ERRORS, $line . ;\n; } } $db-disconnect; close SQL; close ERRORS; That would execute all of the statements that were legal, and put any errored statements in a separate file for you to clean up at your leisure. More elaborate parsing would be required if the statements were multi-line. Daniel J McDonald, CCIE 2495, CNX Principal Network Specialist Digicon Technologies http://www.digicontech.com Digicon, a Cisco Partner, Silver Certified. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
v3: delete based on select
I'm using 3.23.37, and I am taking a random subset of records from a table, and putting them into another table: insert into targettable select * from sourcetable; I then want to delete these records from the source table. I have tried various permutations of: delete from sourcetable select * from sourcetable,targettable where sourcetable.id=targettable.id; but with no luck. The archive only seems to have questions relating to multi-table deletes in v4, and there is nothing about this in the manual or in Carsten's FAQ. Is there a way of doing this? TIA Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Large MySQL setup
Hi, Here is what I understand: your queries are already very optimized, you have already played with database scheme and application optimisations so there is little chance to find any gain there. Your setup seems to be right according to disc I/O and RAM since all indexes fit in RAM and you are not doing much disc I/O. So, your 4 CPUs should be used and, since it is not the case, the only reasons I can figure out are: - Some of your queries are locking the tables. It seems strange since you specify that only SELECTs are run during the day. Anyway, have you checked that this is not the case with 'mysqladmin processlist' ? Yes. Only SELECTs are running. Early on in the month the server is mostly idle, as there is less data in the tables than at the end of the month. During this idle time I am able to do all of the tests that I need to do, including restricting all outside access to ensure only my SELECTs are running. - If you do have table locking problems, you should consider switching from MyISAM to a table format that support row level locking as InnoDB (but I have never tried that myself, I am still using MyISAM on our servers). Right. I have installed InnoDB on my development desktop machine (a Sparc 20 w/ Solaris 2.6) and I am happy with its speed. However, until just a week or so again InnoDB did not support files larger than 4 gig, which was a problem. We will be switching to InnoDB once we get this CPU problem sorted. One major drawback to InnoDB that I have noticed is the limited database size. With a MyISAM table, it grows as needed. Meaning that during the first part of the month, this table is really small, which allows for data processing to be really really fast. In the order of 3 orders of magnitude faster than the end of the month. With InnoDB, your database size is set, and if you have 3 megs of data in it, or 3 gigs, it will take the same amount of time to run. Granted: if there ARE 3 gigs, it will run a lot faster than 3 gigs of MyISAM database tables. - According to MySQL manual, there is a Solaris specific parameter in my.cnf: 'thread_concurrency'. Have you checked this parameter ? (I have no experience with this parameter since I have not used Solaris lately and I am using MySQL with Linux). Yes, the docs say to set this to be double that of the CPUs. The my-huge.cnf file that comes with MySQL has this set to 8 by default. My current my.cnf file uses all of these values, doubled. So my thread-concurrency is currently set to 16. I will experiment more with this value, and see what results it yields by just modify it alone. I appreciate the help, it looks like I will just need to start experimenting with individual values and see what gives me the best results. Thanks again -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Aaron Williams[EMAIL PROTECTED] Black Raven.com http://www.backraven.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 4.0 on FreeBSD?
Hellow everyone, I have got a problem building MySQL 4.0 under FreeBSD 4.3-STABLE. So whats went wrong: I was trying to build mysql from a source tarball which i downloaded, unpacked it and created a standart build script which i use to build mysql on our servers: ==[cat build.sh]== #!/bin/sh CC=gcc CFLAGS=-O2 -fno-strength-reduce \ CXX=gcc CXXFLAGS=-O2 -fno-rtti -fno-exceptions -felide-constructors -fno-strength-r educe \ ./configure \ --prefix=/vhost \ --bindir=/vhost/bin \ --sbindir=/vhost/usr/sbin \ --libexecdir=/vhost/usr/libexec \ --datadir=/vhost/usr/share \ --sysconfdir=/vhost/etc \ --localstatedir=/vhost/usr/db \ --libdir=/vhost/usr/lib \ --includedir=/vhost/usr/include \ --infodir=/vhost/usr/info \ --mandir=/vhost/usr/man \ --enable-assembler \ --with-mysqld-user=mysql \ --with-charset=cp1251 \ --with-extra-charsets=koi8_ru,latin1 \ --with-berkeley-db \ --with-vio \ --with-openssl \ --without-docs \ --without-bench ==[end]== The configure script found everything it needed and i typed gmake and the process began... when it entered the directory sql in source distribution it ended up with an error in the file item_strfunc.cc exactly on 1024 line which says char *tmp=crypt(...) the error was that the function was already declared and should return int (not a char * as a standart unistd.h crypt() function) in config.h everything to my opinion was ok: HAVE_CRYPT 1, HAVE_UNISTD_H 1, HAVE_CRYPT_H not defined. I tried not to build mysql with ssl support and everything went fine... so i can presume that there is a confilct with openssl crypt() and standart crypt() function? Does anyone had a problem like this? I even tried to get the newly sources by using bk, but nothning changed. OpenSSL verion used: 0.9.6 --- Best regards, Alexei V. Alexandrov ElcomSoft Co. Ltd. www.elcomsoft.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
where do you hide the temp files?
Hi all, I am running MySQL 3.23.38 on a Sun Solaris 8 platform. During my program's execution, I get an Lost connection to server error and it terminates. This occurred during creation of an index on an existing table. I'll need help with this error once I get my machine back. When the program terminates, I believe MySQL is still working in the background. The used capacity of my hard drive continues to climb until I run out of disk space. The only way to stop it is to reboot. However, this last time on reboot the machine's hard drive is still full. I've had this happen before and managed to stumble across the temp files that needed to be deleted. No such luck this time. Where are the temp files located when MySQL is doing it's magic? I can't delete (using the mysql tool) any indices I created (ERROR 1030: Got error 28 from table handler) and am basically stuck. Hope I've given enough info. Thanks for all your help, K -- Kay Bowen Tec-Masters, Inc. Advanced Studies and Research Center - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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_file problem
Does the user mysql have search and read permissions to the directroy and file you are trying to load? Venugopal Allavatam wrote: Hi All! I am sorry that I posted that message with a syntactical error. but what i meant to write was this.. insert into table_name(field_name1,data_file) values (1,load_file('/absolute/path/file_name.jpg')); sorry abt the error guys.. but guys this still returns a NULL value. please help. thanks! venu = Venugopal Allavatam Ph: (res.): 949-361-6604 1100 Calle Del Cerro, (mobile): 949-842-1767 Apt.# 123-J, San Clemente, CA-92672 E-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] __ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
'too many connections'
Hi, My website database allows 300 connections, but every once and a while the database stops accepting new connections saying that there is too many. PHP *should* automatically close connections when my scripts end, but perhaps its not doing that. Anyhow, is there a way to have my connections timeout faster, when they're inactive for a short period of time, say 10 seconds or so? Show processlist doesn't show any activity so all 300 of the connections are doing nothing. Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 'too many connections'
Hi, My website database allows 300 connections, but every once and a while the database stops accepting new connections saying that there is too many. PHP *should* automatically close connections when my scripts end, but perhaps its not doing that. Anyhow, is there a way to have my connections timeout faster, when they're inactive for a short period of time, say 10 seconds or so? Show processlist doesn't show any activity so all 300 of the connections are doing nothing. This can be due to your apache configuration. do you use mysql_pconnect function in php. Check you apache setting agains MaxClients StartServers and etc... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: 'too many connections'
No, I'm not using mysql_pconnect, and my apache configuration seems fine. I don't have very many users at the moment so there is no reason for the connections filling up. This can be due to your apache configuration. do you use mysql_pconnect function in php. Check you apache setting agains MaxClients StartServers and etc... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with aggregate query
I'm logging RADIUS detail records to a MySQL database. Currently, I crunch the the detail table (containing individual records) once a month into another table that contains aggregate usage (monthly_usage). CREATE TABLE monthly_usage ( UserName varchar(32) NOT NULL, Realm varchar(64) NOT NULL, UsageDate date NOT NULL, Minutes mediumint unsigned, Logins mediumint unsigned, PRIMARY KEY (Username, Realm, UsageDate) ); The problem is, the detail table is getting to be very large toward the end of the month. I'd like to crunch statistics weekly (or even nightly) to keep disk usage at a reasonable level. I've thought of doing something like: SELECT SUM(detail.AcctSessionTime) + monthly_usage.Minutes FROM detail,monthly_usage WHERE detail.UserName = 'foo' AND monthly_usage.UserName = 'foo'; but this won't work because a user might not always have a row in the monthly_usage table (they might not have logged in yet this month), which makes this query return NULL. My question is this: is there a way to add SUM(detail.AcctSessionTime) to a corresponding row from montly_usage, even if that row from monthly_usage doesn't exist (e.g., add 0 to SUM() if the monthly_usage row doesn't exist). I've considered breaking this up into two queries; one to select all usernames from the detail table and insert dummy rows into monthly_usage with 0 usage, then run the query above, which will then work as expected. This seems kind of kludgy; is there a way to do what I want in a single query? thanks, john -- John Morrissey _o/\ __o [EMAIL PROTECTED]_- \_ / \ \, www.horde.net/__(_)/_(_)/\___(_) /_(_)__ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help with aggregate query
SELECT SUM(d.AcctSessionTime) + IFNULL(m.Minutes, 0) FROM detail d LEFT OUTER JOIN monthly_usage m ON d.UserName = m.UserName WHERE d.UserName = 'foo'; Or, to summarize for all users: SELECT d.UserName as user, SUM(d.AcctSessionTime) + IFNULL(m.Minutes, 0) FROM detail d LEFT OUTER JOIN monthly_usage m ON d.UserName = m.UserName GROUP BY user; --Greg Johnson -Original Message- From: John Morrissey [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 10:45 AM To: [EMAIL PROTECTED] Subject:Help with aggregate query I'm logging RADIUS detail records to a MySQL database. Currently, I crunch the the detail table (containing individual records) once a month into another table that contains aggregate usage (monthly_usage). CREATE TABLE monthly_usage ( UserName varchar(32) NOT NULL, Realm varchar(64) NOT NULL, UsageDate date NOT NULL, Minutes mediumint unsigned, Logins mediumint unsigned, PRIMARY KEY (Username, Realm, UsageDate) ); The problem is, the detail table is getting to be very large toward the end of the month. I'd like to crunch statistics weekly (or even nightly) to keep disk usage at a reasonable level. I've thought of doing something like: SELECT SUM(detail.AcctSessionTime) + monthly_usage.Minutes FROM detail,monthly_usage WHERE detail.UserName = 'foo' AND monthly_usage.UserName = 'foo'; but this won't work because a user might not always have a row in the monthly_usage table (they might not have logged in yet this month), which makes this query return NULL. My question is this: is there a way to add SUM(detail.AcctSessionTime) to a corresponding row from montly_usage, even if that row from monthly_usage doesn't exist (e.g., add 0 to SUM() if the monthly_usage row doesn't exist). I've considered breaking this up into two queries; one to select all usernames from the detail table and insert dummy rows into monthly_usage with 0 usage, then run the query above, which will then work as expected. This seems kind of kludgy; is there a way to do what I want in a single query? thanks, john -- John Morrissey _o/\ __o [EMAIL PROTECTED]_- \_ / \ \, www.horde.net/__(_)/_(_)/\___(_) /_(_)__ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Unique and case-insensitivity with indexes
I am using mySQL 3.22.4a-beta yes, I know its old :-( I have just stumbled across a problem with how it treats 'uniqueness' in table contents. I have a table with a column defined as: create table test (name varchar(80) not null); alter table test ADD UNIQUE (name), ADD INDEX (name); When I now add entries that are case sensitive, I get duplicate entry errors: mysql insert into test (name) values ('a'); Query OK, 1 row affected (0.04 sec) mysql insert into test (name) values ('A'); ERROR 1062: Duplicate entry 'A' for key 1 I always considered the value of 'a' and 'A' to be unique, but this version of mySQL doesn't appear to, at least with indexes. Can anyone either point out what I am doing wrong, or a workaround? TIA Fulko --- Fulko Hew, Voice: 905-681-5570 Senior Engineering Designer, Fax:905-681-5556 SITA (Burlington)Email: [EMAIL PROTECTED] 777 Walkers Line, Burlington, Ontario, Canada, L7N 2G1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Lots of Aborted Connections and Signal 11s
I run PHP 4.0.3 and MySQL 3.23.32. I get a fair amount of traffic and host about 1000 very small sites on a fairly fast server with plenty of RAM and space. However, it keeps shutting down frequently and every time it shuts down, the binary log files rotate, and someone will probably try to access the database while it's down and they'll get an error. Here's a clip from my error log that seems to be repeated over and over and over again, just at different times: BEGIN SNIPPET 011109 7:27:33 Aborted connection 13073 to db: 'site_info' user: 'root' host: `localhost' (Got an error reading communication packets) 011109 7:59:00 Aborted connection 13081 to db: 'site_mails' user: 'root' host: `localhost' (Got an error reading communicat ion packets) 011109 7:59:00 Aborted connection 13080 to db: 'site_mails' user: 'root' host: `localhost' (Got an error reading communicat ion packets) mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. 011109 08:20:45 mysqld restarted /usr/local/mysql/libexec/mysqld: ready for connections 011109 8:57:31 Aborted connection 1622 to db: 'site_mails' user: 'root' host: `localhost' (Got an error reading communicati on packets) 011109 8:57:31 Aborted connection 1654 to db: 'site_mails' user: 'root' host: `localhost' (Got an error reading communicati on packets) 011109 8:57:31 Aborted connection 1806 to db: 'site_users' user: 'root' host: `localhost' (Got an error reading communi cation packets) 011109 8:59:00 Aborted connection 1817 to db: 'site_mails' user: 'root' host: `localhost' (Got an error reading communicati on packets) mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. 011109 09:01:37 mysqld restarted /usr/local/mysql/libexec/mysqld: ready for connections END SNIPPET Any ideas, anyone? I don't use persistent connections, by the way. There's only one page that does use them, and it is accessed about once every couple months. It is not part of the main sites that everyone surfs. - Jonathan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Unique and case-insensitivity with indexes
Fulko Hew wrote: I am using mySQL 3.22.4a-beta yes, I know its old :-( I have just stumbled across a problem with how it treats 'uniqueness' in table contents. I have a table with a column defined as: create table test (name varchar(80) not null); alter table test ADD UNIQUE (name), ADD INDEX (name); If you want this to be case sensitive you need to add BINARY to any '*char' columns: create table test (name varchar(80) BINARY not null); alter table test ADD UNIQUE (name), ADD INDEX (name); b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: MySQL installation problem
database,sql,query,table You have written the following: You need to get gcc-2.95.2 over newer. It is available on the stage.caldera.com site. or http://www.caldera.com/skunkware. Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 3748 Valley Forge Road, Magna Utah 84044 Office 801-250-0795 FAX 801-250-7975 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Unique and case-insensitivity with indexes
If you create your columns with the 'binary' parm (look in the manual), then all the comparisons will be case-sensitive. Can anyone either point out what I am doing wrong, or a workaround? database, mysql, table - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Unique and case-insensitivity with indexes
Bill Adams [EMAIL PROTECTED] replied: Fulko Hew wrote: I am using mySQL 3.22.4a-beta yes, I know its old :-( I have just stumbled across a problem with how it treats 'uniqueness' in table contents. I have a table with a column defined as: create table test (name varchar(80) not null); alter table test ADD UNIQUE (name), ADD INDEX (name); If you want this to be case sensitive you need to add BINARY to any '*char' columns: create table test (name varchar(80) BINARY not null); alter table test ADD UNIQUE (name), ADD INDEX (name); Of course you are right, and despite the fact I've just spent an hour searching the manual for this and _not_ seeing it. I do another search, and yes, there it is staring me in the face. (duh, sound of hand slapping on forehead) --- Fulko Hew, Voice: 905-681-5570 Senior Engineering Designer, Fax:905-681-5556 SITA (Burlington)Email: [EMAIL PROTECTED] 777 Walkers Line, Burlington, Ontario, Canada, L7N 2G1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Unique and case-insensitivity with indexes
12/11/2001 17:34:07, Fulko Hew [EMAIL PROTECTED] wrote: I am using mySQL 3.22.4a-beta yes, I know its old :-( I have just stumbled across a problem with how it treats 'uniqueness' in table contents. I have a table with a column defined as: create table test (name varchar(80) not null); alter table test ADD UNIQUE (name), ADD INDEX (name); When I now add entries that are case sensitive, I get duplicate entry errors: mysql insert into test (name) values ('a'); Query OK, 1 row affected (0.04 sec) mysql insert into test (name) values ('A'); ERROR 1062: Duplicate entry 'A' for key 1 I always considered the value of 'a' and 'A' to be unique, but this version of mySQL doesn't appear to, at least with indexes. Can anyone either point out what I am doing wrong, or a workaround? The workaround is to use the attribute BINARY for your field create table test (myfield varchar(80) BINARY not null, UNIQUE KEY myfield); This way, the index is case sensitive. Unfortunately, this feature was introduced in MySQL 3.23, so your current server is not able to deal with it. Giuseppe TIA Fulko --- Fulko Hew, Voice: 905-681-5570 Senior Engineering Designer, Fax:905-681-5556 SITA (Burlington)Email: [EMAIL PROTECTED] 777 Walkers Line, Burlington, Ontario, Canada, L7N 2G1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Cannot add blob data to innodb table
how do you communicate the binary strings to MySQL? From the manual I found: If you want to insert binary data into a BLOB column, the following characters must be represented by escape sequences: NUL ASCII 0. You should represent this by `\0' (a backslash and an ASCII `0' character). \ ASCII 92, backslash. Represent this by `\\'. ' ASCII 39, single quote. Represent this by `\''. ASCII 34, double quote. Represent this by `\'. If you write C code, you can use the C API function mysql_escape_string() to escape characters for the INSERT statement. See section 8.4.2 C API Function Overview. In Perl, you can use the quote method of the DBI package to convert special characters to the proper escape sequences. See section 8.2.2 The DBI Interface. You should use an escape function on any string that might contain any of the special characters listed above! Try also inserting rows to your table from the mysql command line client. Does that work? Does MyODBC work if you insert simple ASCII strings (not binary strings) in place of the BLOBs: 'jhghjghgjgjgjkgkjhhj'. Regards, Heikki Heikki, Manually inserting text into the blob fields work fine. I was also able to convert the table type from MyISAM to INNODB and the blob fields stayed intact. I guess there must be some unescaped sequence in the blobs that is preventing the insertion of the first record when the table type is innodb. I'm not sure how (or where) you would include an escape function in Access97. I don't see any obvious switch that can be set in MyODBC either. Any suggestions? I will post the problem to the MyODBC list. Thanks, Stephen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: v3: delete based on select
I then want to delete these records from the source table. I have tried various permutations of: delete from sourcetable select * from sourcetable,targettable where sourcetable.id=targettable.id; but with no luck. The archive only seems to have questions relating to multi-table deletes in v4, and there is nothing about this in the manual or in Carsten's FAQ. Erhm, yes, there is - but it's not obvious in your context. You're trying to use a subselect, which isn't supported in MySQL - yet. Is there a way of doing this? At this time, no - not using MySQL directly. If you're using some program to interface w/ MySQL, I would suggest picking up the targettable.id's, collecting these in a comma-separated list and use a query like: DELETE FROM sourcetable WHERE id IN (list). / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Unique and case-insensitivity with indexes
Giuseppe Maxia [EMAIL PROTECTED] responded: The workaround is to use the attribute BINARY for your field create table test (myfield varchar(80) BINARY not null, UNIQUE KEY myfield); This way, the index is case sensitive. Unfortunately, this feature was introduced in MySQL 3.23, so your current server is not able to deal with it. Actually it _is_ documented in my 3.22.x book, so its older than that. It appears to have been added at 3.21.0 --- Fulko Hew, Voice: 905-681-5570 Senior Engineering Designer, Fax:905-681-5556 SITA (Burlington)Email: [EMAIL PROTECTED] 777 Walkers Line, Burlington, Ontario, Canada, L7N 2G1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: myisamchk: error: Checksum for key....
On Mon, Nov 12, 2001 at 03:22:31PM +0200, Sinisa Milivojevic wrote: Grzegorz Paszka writes: Hi. I've problem with mysql. I had i386 RH71 (kernel 2.4.13, 1.5GB RAM, single procesor) with mysql 3.23.36 (now I have 3.23.41 and problem still exists). I run myisamchk on table zawartosc: [root@tygrys rpm]# myisamchk -c -i -v -w zawartosc Checking MyISAM file: zawartosc Data records: 6995929 Deleted blocks: 0 - check file-size - check key delete-chain block_size 1024: - check record delete-chain No recordlinks - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 98% Packed:0% Max levels: 4 - check data record references index: 2 Key: 2: Keyblocks used: 65% Packed: 60% Max levels: 7 Total:Keyblocks used: 69% Packed: 56% - check record links myisamchk: error: Checksum for key: 2 doesn't match checksum for records Record blocks: 6995929Delete blocks: 0 Record data: 398291567Deleted data: 0 Lost space:5259802Linkdata: 26232723 MyISAM-table 'zawartosc' is corrupted Fix it using switch -r or -o Of course I used switch -r and problem still exists. Second I used switch -o and problem still exists. Hi! I hope you have not run myisamchk while server was running Of course. If yes, then that would explain things. Try using CHECK TABLE SQL command instead. I did something like that: mysql check table zawartosc; +---+---+--+ + | Table | Op| Msg_type | Msg_text | +---+---+--+ + | rpm.zawartosc | check | error| Checksum for key: 2 doesn't match checksum for records | | rpm.zawartosc | check | error| Corrupt | +---+---+--+ + 2 rows in set (2 min 56.04 sec) mysql repair table zawartosc; +---++--+--+ | Table | Op | Msg_type | Msg_text | +---++--+--+ | rpm.zawartosc | repair | status | OK | +---++--+--+ 1 row in set (14 min 10.89 sec) mysql check table zawartosc; +---+---+--++ | Table | Op| Msg_type | Msg_text | | +---+---+--++ | rpm.zawartosc | check | error| Checksum for key: 2 doesn't match checksum for |records | | rpm.zawartosc | check | error| Corrupt | | +---+---+--++ 2 rows in set (1 min 58.75 sec) mysql repair table zawartosc EXTENDED; +---++--+--+ | Table | Op | Msg_type | Msg_text | +---++--+--+ | rpm.zawartosc | repair | status | OK | +---++--+--+ 1 row in set (14 min 4.54 sec) mysql check table zawartosc ; +---+---+--++ | Table | Op| Msg_type | Msg_text | | +---+---+--++ | rpm.zawartosc | check | error| Checksum for key: 2 doesn't match checksum for |records | | rpm.zawartosc | check | error| Corrupt | | +---+---+--++ 2 rows in set (2 min 0.96 sec) Do you want to say that there is no way that you can repair MyISAM table ?? Yes, I say more, that I created new database and filled it by perl script from data source and I have the same situation. I think that is the best way of rebuild index file :) So what I should do in this case ? -- Grzegorz - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: myisamchk: error: Checksum for key....
Grzegorz Paszka writes: Yes, I say more, that I created new database and filled it by perl script from data source and I have the same situation. I think that is the best way of rebuild index file :) So what I should do in this case ? -- Grzegorz See in our manual what to do when index file is not there, so try to follow those instructions. Backup your table first !! -- 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
Fulltext search variable
Using 3.23.32 on FreeBsd 3.2 Have gotten a sample fulltext search going, but cannot figure out how to shorten the word length to 2 o3 3, from the default 4. Yes, I've read the docs, and tried the following: Setting variable using mysqld on command line...it won't take it. Looking at the variables - SHOW VARIABLES - it's not in there. ft_min_word_length does not seem to even exist. Can I simply create it somehow? Looking for the config files mentioned in the docs...there is no directory called myisam on my directory tree. looking for the .cnf files...I found: /usr/local/mysql/share/mysql/my-huge.cnf /usr/local/mysql/share/mysql/my-large.cnf /usr/local/mysql/share/mysql/my-medium.cnf /usr/local/mysql/share/mysql/my-small.cnf /usr/local/share/mysql/my-example.cnf Since I was not the original installer of mySQL on the system, I don't know which, if any, conf file is being used. Can anyone offer me help pertaining to this particular distribution? Thanks -- -- Craig Issod HearthNet at http://www.hearth.com [EMAIL PROTECTED]Everything your Hearth Desires -- -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Losing data
Hi I appear to have lost several records, but on doing mysqldump db the records are there. I have tried optimize|repair and the data hasn't re-appeared. Any ideas ? Simon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 stop mysql
Howdy, I'm running version 3.23.41 on RH7.2. For the life of me I can't figure out why I can't stop mysqld. Linuxconf was where I first noticed this where mysqld would not respond to the stop request. I then tried to reboot the box and watched the shutdown process and noticed that mysqld failed the stop request. The only way to 'stop' mysqld is to use the 'killall mysqld' command. Any ideas on what could be wrong. If there is scripting changes, please give me very specific instructions. Thanks.. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load Data with extra field
M. A. Alves writes: mysql database $!#$#%$ I know the feeling. :-P On Sun, 11 Nov 2001, Michael Conley wrote: . . . even though the text files that I am importing don't have the customer number of the person who submitted it, if I know the customer number . . . Where from do you get that information (costumer number) at import time? User input, I'd guess. Anyway, I'd like to add a third way to do it: Create a temporary table, load your data into that table, and then do a INPUT ... SELECT ... where you put that user-input value in the field list of the SELECT, e.g.: INSERT INTO foo (a,b) SELECT 10,b FROM tmptable //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can't start mysql daemon
Description: Error message is: 02 11:31:41 mysqld started InnoDB: Warning: operating system error number 13 in a file operation. InnoDB: Cannot continue operation. 02 11:31:41 mysqld ended How-To-Repeat: Fix: Submitter-Id: Chad Smith [EMAIL PROTECTED] Originator: Organization: Acxiom Corporation MySQL support: none Synopsis: Can't start mysql daemon Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-4.0.0-alpha (Source distribution) Environment: System: AIX aixweb 3 4 0003F7DF4C00 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/powerpc-ibm-aix4.3.3.0/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc -pipe -mcpu=power -Wa,-many' CFLAGS='' CXX='gcc -pipe -mcpu=power -Wa,-many' CXXFLAGS='-felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx 1 bin bin 19 Oct 03 11:48 /lib/libc.a - /usr/ccs/lib/libc.a lrwxrwxrwx 1 bin bin 19 Oct 03 11:48 /usr/lib/libc.a - /usr/ccs/lib/libc.a Configure command: ./configure --prefix=/u01/app/mysql --with-low-memory - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fulltext search variable
Craig Issod wrote: Using 3.23.32 on FreeBsd 3.2 Have gotten a sample fulltext search going, but cannot figure out how to shorten the word length to 2 o3 3, from the default 4. Yes, I've read the docs, and tried the following: Setting variable using mysqld on command line...it won't take it. Looking at the variables - SHOW VARIABLES - it's not in there. ft_min_word_length does not seem to even exist. Can I simply create it somehow? Search: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html for ft_min_word_len (not _length). Looking for the config files mentioned in the docs...there is no directory called myisam on my directory tree. looking for the .cnf files...I found: /usr/local/mysql/share/mysql/my-huge.cnf /usr/local/mysql/share/mysql/my-large.cnf /usr/local/mysql/share/mysql/my-medium.cnf /usr/local/mysql/share/mysql/my-small.cnf /usr/local/share/mysql/my-example.cnf Since I was not the original installer of mySQL on the system, I don't know which, if any, conf file is being used. The file needs to be named 'my.cnf'. Please Read: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#Option_files b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't stop mysql
[EMAIL PROTECTED] wrote: Howdy, I'm running version 3.23.41 on RH7.2. For the life of me I can't figure out why I can't stop mysqld. Linuxconf was where I first noticed this where mysqld would not respond to the stop request. I then tried to reboot the box and watched the shutdown process and noticed that mysqld failed the stop request. The only way to 'stop' mysqld is to use the 'killall mysqld' command. Any ideas on what could be wrong. If there is scripting changes, please give me very specific The program safe_mysqld will restart mysqld everytime it crashes or is killed. You really should stop mysql with 'mysqladmin shutdown'. b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't stop mysql
I'm having the same problem. At 12:27 PM 11/12/2001 -0500, [EMAIL PROTECTED] wrote: Howdy, I'm running version 3.23.41 on RH7.2. For the life of me I can't figure out why I can't stop mysqld. Linuxconf was where I first noticed this where mysqld would not respond to the stop request. I then tried to reboot the box and watched the shutdown process and noticed that mysqld failed the stop request. The only way to 'stop' mysqld is to use the 'killall mysqld' command. Any ideas on what could be wrong. If there is scripting changes, please give me very specific instructions. Thanks.. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't stop mysql
i don't use linuxconf. if u want to stop mysql, execute : /ect/init.d/mysql stop easiest :) Trong Phuc - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 12, 2001 9:27 AM Subject: Can't stop mysql Howdy, I'm running version 3.23.41 on RH7.2. For the life of me I can't figure out why I can't stop mysqld. Linuxconf was where I first noticed this where mysqld would not respond to the stop request. I then tried to reboot the box and watched the shutdown process and noticed that mysqld failed the stop request. The only way to 'stop' mysqld is to use the 'killall mysqld' command. Any ideas on what could be wrong. If there is scripting changes, please give me very specific instructions. Thanks.. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fulltext search variable
Craig Issod wrote: Craig Issod wrote: Using 3.23.32 on FreeBsd 3.2 Have gotten a sample fulltext search going, but cannot figure out how to shorten the word length to 2 o3 3, from the default 4. Yes, I've read the docs, and tried the following: Setting variable using mysqld on command line...it won't take it. Looking at the variables - SHOW VARIABLES - it's not in there. ft_min_word_length does not seem to even exist. Can I simply create it somehow? The file needs to be named 'my.cnf'. Please Read: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#Option_files Will adding variable line to one of the sample .cnf files, moving and renaming that file to my data directory and then restarting the server work? I think so. You want to add it as set-variable = ft_min_word_len=3 without the quotes AND (if you check out the docs on said variable) you /must/ rebuild the index before it will take effect. Restarting the server will have it re-read the config file, then you want to rebuild the index. b. mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL installation problem
- Original Message - From: Boyd Lynn Gerber [EMAIL PROTECTED] To: é×ÁÎ ðÏÎÏÍÁÒÅ× [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, November 11, 2001 5:57 PM Subject: Re: MySQL installation problem You need to get gcc-2.95.2 over newer. It is available on the stage.caldera.com site. or http://www.caldera.com/skunkware. I do this, but message is the same: gcc -03 -DDBUG_OFF -DSCO -O conf_to_src conf_tosrc.o -lcrypt -lsocket -lm Undefined first referenced symbol in file main /usr/ccs/lib/crt1.o conf_to_src: fatal error: Symbol referencing errors. No output written to conf_to_src ***Error code 1 (bu21) ***Error code 1 (bu21) ***Error code 1 (bu21) (I install MySQL 3.23.43 on SCO Open Server 3.2 v5.0.4 Other software: FSU Pthreads 3.5, gcc-2.95.2, GNU Make 3.78.1) Can You help me? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Losing data
Simon Windsor wrote: Hi I appear to have lost several records, but on doing mysqldump db the records are there. I have tried optimize|repair and the data hasn't re-appeared. Any ideas ? Not with this level of information. (Try including some sql, what you are trying to match, etc.. And post to the list!) b. mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
show processlist: status
I'm trying to understand what the status field of the SHOW PROCESSLIST command means. When I execute a query - SELECT count(*) as count from table1 where col1 like name%;, the query takes a long time to execute. The table has over 47,000,000 records and is indexed on col1. What does the status sending data mean? It doesn't seem like there is that much data that needs to be sent for a count query. Thanks, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 stop mysql
[EMAIL PROTECTED] wrote: I'll agree with you but - when I shutdown my linux box or restart it, it tries to stop the mysql server and it can't. The command /etc/init.d/mysqld stop should work and it isn't. The question is why can't I stop the mysql server with this command. As a matter of fact, using /etc/init.d/mysqld stop restart fails when it tries the stop. There has to be something convoluted in one of the script files. Besides that, I'm not the only one with this issue. thanks This is really a distribution issue then. But I would look inside the init.d/mysqld script and see if it is calling mysqladmin or not. If it is: Does it have the full path to mysqladmin? Did you set the root password in MySQL and now need to specify it in the file, e.g.: mysqladmin -pthe.root?password shutdown? b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fulltext search variable
Hi! On Nov 12, Craig Issod wrote: Using 3.23.32 on FreeBsd 3.2 Have gotten a sample fulltext search going, but cannot figure out how to shorten the word length to 2 o3 3, from the default 4. Yes, I've read the docs, and tried the following: Setting variable using mysqld on command line...it won't take it. Looking at the variables - SHOW VARIABLES - it's not in there. ft_min_word_length does not seem to even exist. Can I simply create it somehow? Online manual is for the latest MySQL version - that is 4.0.0. In 3.23 the only way to change this value was to recompile MySQL from sources. See the manual that came with your MySQL version. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL installation problem
On Mon, 12 Nov 2001, ? wrote: You need to get gcc-2.95.2 over newer. It is available on the stage.caldera.com site. or http://www.caldera.com/skunkware. I do this, but message is the same: gcc -03 -DDBUG_OFF -DSCO -O conf_to_src conf_tosrc.o -lcrypt -lsocket -lm Undefined first referenced symbol in file main /usr/ccs/lib/crt1.o conf_to_src: fatal error: Symbol referencing errors. No output written to conf_to_src ***Error code 1 (bu21) ***Error code 1 (bu21) ***Error code 1 (bu21) (I install MySQL 3.23.43 on SCO Open Server 3.2 v5.0.4 Other software: FSU Pthreads 3.5, gcc-2.95.2, GNU Make 3.78.1) Can You help me? Did you install the libraries and linkers from the CD. That file is part of the libraries and linkers. Also have you installed all the patches? I just did a complete make with out any problems on my OSR 5.0.4 system. I am using GNU Make 3.79.1 and FSU Pthreads 3.8. Foy can get the 3.8. from. I know that 5.0.4 and pervious versions are very buggy. Unfortunately my ftp.zenez.com site is down. The site below has many of what I consider to be the most important files. ftp://ftp.lerctr.org/pub/zenez Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 3748 Valley Forge Road, Magna Utah 84044 Office 801-250-0795 FAX 801-250-7975 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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_file problem
Hi! I am stuck with the load_file problem... I have created the following table : create table table_name (field_number1 int unsigned primary key not null, data_field longblob not null); I then tried to load the contents of a file with permissions -rw-r--r-- located in the /home/user directory on my Red Hat 7.1 version of linux using the following statements... insert into table_name values (1, load_file('/home/user/file_name')); or insert into table_name values (1, load_file(/home/user/file_name)); (both produce the same result) it gives me an error that a null cannot be stored as I set it that way in the table creation statement. It also means that the load_file function is returning a null. I am using the 3.23.36 of mysql. I need suggestions. Thank you Venu = Venugopal Allavatam Ph: (res.): 949-361-6604 1100 Calle Del Cerro, (mobile): 949-842-1767 Apt.# 123-J, San Clemente, CA-92672 E-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] __ Do You Yahoo!? Find a job, post your resume. http://careers.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
Sub Count
Howdy, I'm trying to write a select statement that produces a SUB COUNT of column PORT_STATUS where (A) PORT_STATUS = 'up' and (B) PORT_STATUS = 'down'. I'd like to do this in one statment with GROUP by LOCATION_T.ADDRESS, LOCATION_T.CITY without altering the outer select. This sort of thing is simple to do with PL/SQL. However, this is a mysql database with select only. Is it somehow possible to do a sub select into a variable i.e. -- SELECT COUNT(DISTINCT COLUMN) INTO VARIABLE WHERE COLUMN = 'up' ? SELECT LOCATION_T.ADDRESS , LOCATION_T.CITY , COUNT(DISTINCT -- COUNT THE NUMBER OF ROUTERS AT EACH LOCATION , COUNT(DISTINCT -- COUNT TOTAL # OF SLOTS AT EACH LOCATION , COUNT(DISTINCT -- COUNT TOTAL # OF PORTS AT EACH LOCATION , COUNT(DISTINCT -- ?? SEPARATE COUNT WHERE HARDWARE_T.PORT_STATUS = 'up' FOR EACH LOCATION ?? , COUNT(DISTINCT -- ?? SEPARATE COUNT WHERE HARDWARE_T.PORT_STATUS = 'down' FOR EACH LOCATION ?? FROM LOCATION_T, HARDWARE_T WHERE LOCATION_T.IP = HARDWARE_T.IP GROUP BY LOCATION_T.ADDRESS, LOCATION_T.CITY Output to look like: ADDRESS CITY Number of Routers Number of Slots Number of Ports Number of Ports UP Number of Ports DOWN ___ ___ ___ 32 StreetNew York 8 90 300 150 150 52 StreetNew York 12 120 400 200 200 Thanks in advance.. Brad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with aggregate query
On Mon, Nov 12, 2001 at 11:14:21AM -0500, Johnson, Gregert wrote: % SELECT SUM(d.AcctSessionTime) + IFNULL(m.Minutes, 0) % FROM detail d LEFT OUTER JOIN monthly_usage m ON d.UserName = m.UserName % WHERE d.UserName = 'foo'; % % Or, to summarize for all users: % % SELECT d.UserName as user, SUM(d.AcctSessionTime) + IFNULL(m.Minutes, 0) % FROM detail d LEFT OUTER JOIN monthly_usage m ON d.UserName = m.UserName % GROUP BY user; D'oh, I forgot something in my post. monthly_usage actually contains one row per month per user. I need to add the SUM() from detail to the monthly_usage row for the *current month*. This query: SELECT SUM(detail.AcctSessionTime) + IFNULL(monthly_usage.Minutes, 0) FROM detail LEFT OUTER JOIN monthly_usage ON detail.UserName = monthly_usage.UserName WHERE detail.UserName = 'johnmorr' AND monthly_usage.UserName = 'johnmorr' AND monthly_usage.UsageDate = '2001-11-01'; fails because the user may not have a row in monthly_usage for the current month, so I'm not really sure that I can JOIN the two tables (the WHERE clause won't match if the row doesn't exist). john -- John Morrissey _o/\ __o [EMAIL PROTECTED]_- \_ / \ \, www.horde.net/__(_)/_(_)/\___(_) /_(_)__ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
primary key based on unique value for two columns
I would like to have a table that has a primary key defined on a combination of two columns in the table. In other words a unique key based upon the values in two columns. I don't think I am able to do this in mysql. I think you can only have a primary key on one column and not on a combination of columns. If I am wrong please correct me. I am looking for work arounds. I have thought of one. That would be to concatenate the columns and use a field terminator such as a - to separate the values or (columns) in the single column. Then you could use string functions to parse the columns. Ex: For column 1 in concatenated column left( ColumnName, (instr( ColumnName, '-') - 1) ) For column 2 in concatenated column Substr (ColumName, (instr ( ColumnName, '-') + 1) , length(ColumnName)- (instr ( ColumnName, '-') + 1) ) Is there any other way to do this? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 stop mysql
The only reference to mysqladmin is for the reload at the end. I think this whole thing started after doing the mysql_install_db and then creating the root password - but it may be a coincidence. Any and all help is welcome as to why /etc/init.d/mysqld stop fails. The /etc/init.d/mysql is as follows: #!/bin/bash # # mysqldThis shell script takes care of starting and stopping # the MySQL subsystem (mysqld). # # chkconfig: - 78 12 # description: MySQL database server. # processname: mysqld # config: /etc/my.cnf # pidfile: /var/run/mysqld/mysqld.pid # Source function library. . /etc/rc.d/init.d/functions # Source networking configuration. . /etc/sysconfig/network # Source subsystem configuration. [ -f /etc/sysconfig/subsys/mysqld ] . /etc/sysconfig/subsys/mysqld prog=MySQL start(){ touch /var/log/mysqld.log chown mysql.mysql /var/log/mysqld.log chmod 0640 /var/log/mysqld.log if [ ! -d /var/lib/mysql/mysql ] ; then action $Initializing MySQL database: /usr/bin/mysql_install_db ret=$? chown -R mysql.mysql /var/lib/mysql if [ $ret -ne 0 ] ; then return $ret fi fi chown -R mysql.mysql /var/lib/mysql chmod 0755 /var/lib/mysql /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf /dev/null 21 ret=$? if [ $ret -eq 0 ]; then action $Starting $prog: /bin/true else action $Starting $prog: /bin/false fi [ $ret -eq 0 ] touch /var/lock/subsys/mysqld return $ret } stop(){ /bin/kill `cat /var/run/mysqld/mysqld.pid 2 /dev/null ` /dev/null 21 ret=$? if [ $ret -eq 0 ]; then action $Stopping $prog: /bin/true else action $Stopping $prog: /bin/false fi [ $ret -eq 0 ] rm -f /var/lock/subsys/mysqld [ $ret -eq 0 ] rm -f /var/lib/mysql/mysql.sock return $ret } restart(){ stop start } condrestart(){ [ -e /var/lock/subsys/mysqld ] restart || : } reload(){ [ -e /var/lock/subsys/mysqld ] mysqladmin reload } # See how we were called. case $1 in start) start ;; stop) stop ;; status) status mysqld ;; reload) reload ;; restart) restart ;; condrestart) condrestart ;; *) echo $Usage: $0 {start|stop|status|reload|condrestart|restart} exit 1 esac exit $? Bill Adams [EMAIL PROTECTED] on 11/12/2001 01:11:37 PM To: Lad Gaal/MarconiMedical@Marconi, Mysql List [EMAIL PROTECTED] cc: Subject: Re: Can't stop mysql [EMAIL PROTECTED] wrote: I'll agree with you but - when I shutdown my linux box or restart it, it tries to stop the mysql server and it can't. The command /etc/init.d/mysqld stop should work and it isn't. The question is why can't I stop the mysql server with this command. As a matter of fact, using /etc/init.d/mysqld stop restart fails when it tries the stop. There has to be something convoluted in one of the script files. Besides that, I'm not the only one with this issue. thanks This is really a distribution issue then. But I would look inside the init.d/mysqld script and see if it is calling mysqladmin or not. If it is: Does it have the full path to mysqladmin? Did you set the root password in MySQL and now need to specify it in the file, e.g.: mysqladmin -pthe.root?password shutdown? b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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_file problem
Are you trying to load the contents of a file with an INSERT statement? If so, you can't. The values MUST be constants. What are your really trying to do? -Original Message- From: Venugopal Allavatam [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 12:26 PM To: Sinisa Milivojevic Cc: [EMAIL PROTECTED] Subject: load_file problem Hi! I am stuck with the load_file problem... I have created the following table : create table table_name (field_number1 int unsigned primary key not null, data_field longblob not null); I then tried to load the contents of a file with permissions -rw-r--r-- located in the /home/user directory on my Red Hat 7.1 version of linux using the following statements... insert into table_name values (1, load_file('/home/user/file_name')); or insert into table_name values (1, load_file(/home/user/file_name)); (both produce the same result) it gives me an error that a null cannot be stored as I set it that way in the table creation statement. It also means that the load_file function is returning a null. I am using the 3.23.36 of mysql. I need suggestions. Thank you Venu = Venugopal Allavatam Ph: (res.): 949-361-6604 1100 Calle Del Cerro, (mobile): 949-842-1767 Apt.# 123-J, San Clemente, CA-92672 E-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] __ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Managing replication
I have a fairly complicated one-way replication set-up, whereby we have multiple master servers and multiple slaves, all replicating unrelated databases from one to another. It's set up and works, but not reliably. Pretty much every day I come into work to find one machine is no longer replicating, or there are inconsistencies in the replicated data. In fact, it's gotten so complicated that I had to write a PHP script to monitor each of the 8 masters/slaves to see bin-log numbers and slave status! Does anyone on the list have a decent web-based management tool for MySQL replication? I am also looking for suggestions on the following topics: 1) Binary log management. We often get binlogs in the 1GB range after only a day's transactions. I really need to figure out an easy, painless process to rotate these out without losing updates, which leads to 2)... 2) Re-syncing tables. After a failure of replication, I need to figure out how to easily re-sync the tables in a database. I'd really like to run a batch job nightly to re-sync without screwing up replication. But we're talking 40 tables or so in some cases. 3) Optimizing binlogs in the form of table restriction. I only want to replicate very specific tables on the master, but from what I can tell from the documentation, the binlog-do-db command from /etc/my.cnf allows you to only specify a single database - you can't have multiple binlog-do-db commands like the other commands. Is there anything I can do? We're running various versions - 3.23.38 through 3.23.43 - under FreeBSD 4.4. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 stop mysql
[EMAIL PROTECTED] wrote: The only reference to mysqladmin is for the reload at the end. I think this whole thing started after doing the mysql_install_db and then creating the root password - but it may be a coincidence. Any and all help is welcome as to why /etc/init.d/mysqld stop fails. The /etc/init.d/mysql is as follows: I would update the script: stop(){ /path/to/mysqladmin -uroot -pyour?root.password shutdown /dev/null 21 ret=$? if [ $ret -eq 0 ]; then action $Stopping $prog: /bin/true else action $Stopping $prog: /bin/false fi [ $ret -eq 0 ] rm -f /var/lock/subsys/mysqld [ $ret -eq 0 ] rm -f /var/lib/mysql/mysql.sock return $ret } restart(){ stop start } condrestart(){ [ -e /var/lock/subsys/mysqld ] restart || : } reload(){ [ -e /var/lock/subsys/mysqld ] mysqladmin -uroot -pyour?root.password reload } b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Cannot add blob data to innodb table
Hi! At 08:57 AM 11/12/01 -0800, you wrote: how do you communicate the binary strings to MySQL? From the manual I found: If you want to insert binary data into a BLOB column, the following characters must be represented by escape sequences: NUL ASCII 0. You should represent this by `\0' (a backslash and an ASCII `0' character). \ ASCII 92, backslash. Represent this by `\\'. ' ASCII 39, single quote. Represent this by `\''. ASCII 34, double quote. Represent this by `\'. If you write C code, you can use the C API function mysql_escape_string() to escape characters for the INSERT statement. See section 8.4.2 C API Function Overview. In Perl, you can use the quote method of the DBI package to convert special characters to the proper escape sequences. See section 8.2.2 The DBI Interface. You should use an escape function on any string that might contain any of the special characters listed above! Try also inserting rows to your table from the mysql command line client. Does that work? Does MyODBC work if you insert simple ASCII strings (not binary strings) in place of the BLOBs: 'jhghjghgjgjgjkgkjhhj'. Regards, Heikki Heikki, Manually inserting text into the blob fields work fine. I was also able to convert the table type from MyISAM to INNODB and the blob fields stayed intact. I guess there must be some unescaped sequence in the blobs that is preventing the insertion of the first record when the table type is innodb. I'm not sure how (or where) you would include an escape function in Access97. I don't see any obvious switch that can be set in MyODBC either. Any suggestions? I will post the problem to the MyODBC list. You could try to insert the first row from the command line: cut it from the query log and feed it to mysql, on Unix e.g.: mysql yourdatabasename atextfilecontainingtheinsertstatement The query log seemed to contain correctly escaped characters. Or you can send the insert statement to [EMAIL PROTECTED] who is responsible for MyODBC, I think. I am Ccing this email to Venu. Thanks, Stephen Regards, Heikki http://www.innodb.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: load_file problem
oops...I'm wrong...you can load that way. -Original Message- From: Rick Emery Sent: Monday, November 12, 2001 12:56 PM To: [EMAIL PROTECTED] Subject: RE: load_file problem Are you trying to load the contents of a file with an INSERT statement? If so, you can't. The values MUST be constants. What are your really trying to do? -Original Message- From: Venugopal Allavatam [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 12:26 PM To: Sinisa Milivojevic Cc: [EMAIL PROTECTED] Subject: load_file problem Hi! I am stuck with the load_file problem... I have created the following table : create table table_name (field_number1 int unsigned primary key not null, data_field longblob not null); I then tried to load the contents of a file with permissions -rw-r--r-- located in the /home/user directory on my Red Hat 7.1 version of linux using the following statements... insert into table_name values (1, load_file('/home/user/file_name')); or insert into table_name values (1, load_file(/home/user/file_name)); (both produce the same result) it gives me an error that a null cannot be stored as I set it that way in the table creation statement. It also means that the load_file function is returning a null. I am using the 3.23.36 of mysql. I need suggestions. Thank you Venu = Venugopal Allavatam Ph: (res.): 949-361-6604 1100 Calle Del Cerro, (mobile): 949-842-1767 Apt.# 123-J, San Clemente, CA-92672 E-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] __ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql+java: how to use LOAD DATA LOCAL INFILE
Hi ! If off-topic, forgive me, please. Environment: Win98 / mysql3.23.42-max / mm.mysql-2.0.7 / jdk1.3.1 Description: I'm trying to load data from file to table using following query: LOAD DATA LOCAL INFILE 'C:\\WINDOWS\\TEMP\\rec222.txt' INTO TABLE ext_call_info_ip FIELDS TERMINATED BY '\t' IGNORE 1 LINES (id, pin, ani, phone_number, extended_reason_code, duration, dialed_number, service_code, total_units, start_time, disconnect_reason); It works fine when I'm doing this in mysql client. But whan I'm trying to do it from java class: Statement st = db.connection.createStatement(); String query = LOAD DATA LOCAL INFILE 'C:WINDOWSTEMPrec222.txt' + INTO TABLE ext_call_info_ip FIELDS TERMINATED BY '\t' IGNORE 1 LINES + (id, pin, ani, phone_number, extended_reason_code, + duration, dialed_number, service_code, total_units, + start_time, disconnect_reason); st.executeUpdate( query ); I got: SQLException: Invalid authorization specification: Access denied for user: 'xxx@localhost' (Using password: YES) In both situations I'm doing it on thesame user (xxx@localhost). When I'm trying to do it in java, but with user root@localhost, I got no exception, but 0 records are loaded. Question: 1) How can I use LOAD DATA LOCAL INFILE ... with java ? 2) Why this exception (access denided) ? - Pawe Szczerba mailto:[EMAIL PROTECTED] +48 603 651 731 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: primary key based on unique value for two columns
Yes, you can have multi-column keys. see the manual, para. 6.5.3 CREATE TABLE Syntax -Original Message- From: Brendin [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 12:37 PM To: [EMAIL PROTECTED] Subject: primary key based on unique value for two columns I would like to have a table that has a primary key defined on a combination of two columns in the table. In other words a unique key based upon the values in two columns. I don't think I am able to do this in mysql. I think you can only have a primary key on one column and not on a combination of columns. If I am wrong please correct me. I am looking for work arounds. I have thought of one. That would be to concatenate the columns and use a field terminator such as a - to separate the values or (columns) in the single column. Then you could use string functions to parse the columns. Ex: For column 1 in concatenated column left( ColumnName, (instr( ColumnName, '-') - 1) ) For column 2 in concatenated column Substr (ColumName, (instr ( ColumnName, '-') + 1) , length(ColumnName)- (instr ( ColumnName, '-') + 1) ) Is there any other way to do this? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: primary key based on unique value for two columns
Brendin wrote: I would like to have a table that has a primary key defined on a combination of two columns in the table. In other words a unique key based upon the values in two columns. I don't think I am able to do this in mysql. I think you can only have a primary key on one column and not on a combination of columns. If I am wrong please correct me. At least in 3.23.x (x?) and higher you CAN have a primary key on multiple columns. I am looking for work arounds. I have thought of one. That would be to concatenate the columns and use a field terminator such as a - to separate the values or (columns) in the single column. Then you could use string functions to parse the columns. Ex: [snip] There is a maximum key lenth so if you have two char(255) columns you might need to do something like: ALTER TABLE table ADD PRIMARY KEY( char_col1(100), char_col2(100)); However, this also means that the combination of the first 100 chars from each column must be unique. b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: primary key based on unique value for two columns
Hi You are wrong. Just try for example: create table test( pk1 int not null, pk2 int not null, primary key(pk1,pk2) ); Regards Daniel £a e-direct Polska sp. z o.o. WWW: http://www.e-direct.pl E-mail: [EMAIL PROTECTED] 45-072 Opole ul. Reymonta 45 tel. +48 77 44 26 073 fax. +48 77 44 26 074 -Original Message- From: Brendin [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 7:37 PM To: [EMAIL PROTECTED] Subject: primary key based on unique value for two columns I would like to have a table that has a primary key defined on a combination of two columns in the table. In other words a unique key based upon the values in two columns. I don't think I am able to do this in mysql. I think you can only have a primary key on one column and not on a combination of columns. If I am wrong please correct me. I am looking for work arounds. I have thought of one. That would be to concatenate the columns and use a field terminator such as a - to separate the values or (columns) in the single column. Then you could use string functions to parse the columns. Ex: For column 1 in concatenated column left( ColumnName, (instr( ColumnName, '-') - 1) ) For column 2 in concatenated column Substr (ColumName, (instr ( ColumnName, '-') + 1) , length(ColumnName)- (instr ( ColumnName, '-') + 1) ) Is there any other way to do this? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: primary key based on unique value for two columns
I would like to have a table that has a primary key defined on a combination of two columns in the table. In other words a unique key based upon the values in two columns. I don't think I am able to do this in mysql. I think you can only have a primary key on one column and not on a combination of columns. If I am wrong please correct me. You're wrong :-) mysql CREATE TABLE tablename (col_a int not null, - b int not null, PRIMARY KEY (a, b)); I am looking for work arounds. No need to... / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: primary key based on unique value for two columns
You can't use a primary key for that, but you CAN make a unique two-column key: ALTER TABLE MyTable ADD UNIQUE MyNewIndex (Column1,Column2) - Jonathan -Original Message- From: Brendin [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 10:37 AM To: [EMAIL PROTECTED] Subject: primary key based on unique value for two columns I would like to have a table that has a primary key defined on a combination of two columns in the table. In other words a unique key based upon the values in two columns. I don't think I am able to do this in mysql. I think you can only have a primary key on one column and not on a combination of columns. If I am wrong please correct me. I am looking for work arounds. I have thought of one. That would be to concatenate the columns and use a field terminator such as a - to separate the values or (columns) in the single column. Then you could use string functions to parse the columns. Ex: For column 1 in concatenated column left( ColumnName, (instr( ColumnName, '-') - 1) ) For column 2 in concatenated column Substr (ColumName, (instr ( ColumnName, '-') + 1) , length(ColumnName)- (instr ( ColumnName, '-') + 1) ) Is there any other way to do this? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: primary key based on unique value for two columns
Will this relate the primary keys to one another or just create primary keys on the columns. Ie: Create table test (column1 int(11) not null, column2 int(11) not null, primary key (column1, column2) ) Then could you... Insert into test values (1,2) Insert into test values (1,3) Note that the combination of the two columns represents a distinct value but column 1 is the same value in both inserts. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Bill Adams Sent: Monday, November 12, 2001 12:05 PM To: Brendin Cc: [EMAIL PROTECTED] Subject: Re: primary key based on unique value for two columns Brendin wrote: I would like to have a table that has a primary key defined on a combination of two columns in the table. In other words a unique key based upon the values in two columns. I don't think I am able to do this in mysql. I think you can only have a primary key on one column and not on a combination of columns. If I am wrong please correct me. At least in 3.23.x (x?) and higher you CAN have a primary key on multiple columns. I am looking for work arounds. I have thought of one. That would be to concatenate the columns and use a field terminator such as a - to separate the values or (columns) in the single column. Then you could use string functions to parse the columns. Ex: [snip] There is a maximum key lenth so if you have two char(255) columns you might need to do something like: ALTER TABLE table ADD PRIMARY KEY( char_col1(100), char_col2(100)); However, this also means that the combination of the first 100 chars from each column must be unique. b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: primary key based on unique value for two columns
This will work thanks... That's what I want a unique key based on two columns. -Original Message- From: Jonathan Hilgeman [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 12:09 PM To: 'Brendin'; [EMAIL PROTECTED] Subject: RE: primary key based on unique value for two columns You can't use a primary key for that, but you CAN make a unique two-column key: ALTER TABLE MyTable ADD UNIQUE MyNewIndex (Column1,Column2) - Jonathan -Original Message- From: Brendin [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 10:37 AM To: [EMAIL PROTECTED] Subject: primary key based on unique value for two columns I would like to have a table that has a primary key defined on a combination of two columns in the table. In other words a unique key based upon the values in two columns. I don't think I am able to do this in mysql. I think you can only have a primary key on one column and not on a combination of columns. If I am wrong please correct me. I am looking for work arounds. I have thought of one. That would be to concatenate the columns and use a field terminator such as a - to separate the values or (columns) in the single column. Then you could use string functions to parse the columns. Ex: For column 1 in concatenated column left( ColumnName, (instr( ColumnName, '-') - 1) ) For column 2 in concatenated column Substr (ColumName, (instr ( ColumnName, '-') + 1) , length(ColumnName)- (instr ( ColumnName, '-') + 1) ) Is there any other way to do this? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Fulltext search - How to index c++
Hi. What do I need to do to make FTS index things like c++? I have tried setting ft_min_word_len=0 and ft_min_word_len=1 in /etc/my.cnf. Restarted the mysqld and did a RENAME on the table followed by a new CREATE TABLE and an INSERT ... SELECT to import the data back into the correctly named table, but I still cannot search for c, any other 1 character term or c++ in my data set. I can search for two-character terms fine, though... Is this not possible, or have I missed a setting somewhere? Regards. Gordan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: show processlist: status
Hi! I'm trying to understand what the status field of the SHOW PROCESSLIST command means. When I execute a query - SELECT count(*) as count from table1 where col1 like name%;, the query takes a long time to execute. The table has over 47,000,000 records and is indexed on col1. What does the status sending data mean? It doesn't seem like there is that much data that needs to be sent for a count query. I think it means it is 'sending' rows to the count operator, not to the user. Thanks, Jeff Regards, Heikki http://www.innodb.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: Losing data
Hi The OS is Redhat 7.1 on a dual processor Pentium box, running MySQL 3.23.36, the standard RedHat version. The machine is running two databases, one is a full archive while the other ones holds current data. The same five records are unavailable using SQL in the two databases, but using mysqldump I can see the records. Obviously the data file is OK, but the index records are corrupt. What can I do ? - Export the databases, drop originals and reload. - Drop indexes and rebuild ? - Repair files ? Which ones, data or index or both ? What is advisable and will involve the least work ? Simon On Monday 12 November 2001 5:20 pm, Simon Windsor wrote: Hi I appear to have lost several records, but on doing mysqldump db the records are there. I have tried optimize|repair and the data hasn't re-appeared. Any ideas ? Simon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: primary key based on unique value for two columns
sure you can CREATE TABLE xx (field1 INT NOT NULL,field2 INT NOT NULL,PRIMARY KEY (field1,field2)) and you have table xx with unique key in two fields hand primoz - Original Message - From: Brendin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 12, 2001 7:37 PM Subject: primary key based on unique value for two columns I would like to have a table that has a primary key defined on a combination of two columns in the table. In other words a unique key based upon the values in two columns. I don't think I am able to do this in mysql. I think you can only have a primary key on one column and not on a combination of columns. If I am wrong please correct me. I am looking for work arounds. I have thought of one. That would be to concatenate the columns and use a field terminator such as a - to separate the values or (columns) in the single column. Then you could use string functions to parse the columns. Ex: For column 1 in concatenated column left( ColumnName, (instr( ColumnName, '-') - 1) ) For column 2 in concatenated column Substr (ColumName, (instr ( ColumnName, '-') + 1) , length(ColumnName)- (instr ( ColumnName, '-') + 1) ) Is there any other way to do this? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Natural Sort
Looking thru the MYSQL archives I see a few people had interest in a natural sort method to sort text fields containing numbers. I to would like this feature. Has anything been done for this? Currently sorts text like: 1200 - A tale of 3 dogs 3 bright lights go on Should be: 3 bright lights go on 1200 - A tale of 3 dogs Thanks, Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 stop mysql / possible bug?
I changed the script and now all is well. However I have two concerns: 1) Paranoid about the password being in this script. Is there a way around this. 2) Since I had to change the script to make it work, Is there a bug in 3.23.41?? Thanks for pointing me in the right direction. Bill Adams [EMAIL PROTECTED] on 11/12/2001 01:58:18 PM To: Lad Gaal/MarconiMedical@Marconi cc: Mysql List [EMAIL PROTECTED] Subject: Re: Can't stop mysql [EMAIL PROTECTED] wrote: The only reference to mysqladmin is for the reload at the end. I think this whole thing started after doing the mysql_install_db and then creating the root password - but it may be a coincidence. Any and all help is welcome as to why /etc/init.d/mysqld stop fails. The /etc/init.d/mysql is as follows: I would update the script: stop(){ /path/to/mysqladmin -uroot -pyour?root.password shutdown /dev/null 21 ret=$? if [ $ret -eq 0 ]; then action $Stopping $prog: /bin/true else action $Stopping $prog: /bin/false fi [ $ret -eq 0 ] rm -f /var/lock/subsys/mysqld [ $ret -eq 0 ] rm -f /var/lib/mysql/mysql.sock return $ret } restart(){ stop start } condrestart(){ [ -e /var/lock/subsys/mysqld ] restart || : } reload(){ [ -e /var/lock/subsys/mysqld ] mysqladmin -uroot -pyour?root.password reload } b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 stop mysql / possible bug?
[EMAIL PROTECTED] wrote: I changed the script and now all is well. However I have two concerns: 1) Paranoid about the password being in this script. Is there a way around this. chown root:root /etc/rc.d/init.d/mysqld chmod go-rx /etc/rc.d/init.d/mysqld 2) Since I had to change the script to make it work, Is there a bug in 3.23.41?? It is a bug in the script NOT in MySQL its self. (Note that this script AFAIK is not a standard script that comes with MySQL, your distribution probably added it.) Killing safe_mysqld or the mysqld processes is dangerous and wrong. b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: primary key based on unique value for two columns
I have not tested this with the primary key - my previous suggestion went off the logic in my head at the time (most likely not a good thing, since I'm tired right now), but I have a feeling Bill here is probably correct if he says it's possible with the primary key as well. I stand corrected. - Jonathan -Original Message- From: Bill Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 11:05 AM To: Brendin Cc: [EMAIL PROTECTED] Subject: Re: primary key based on unique value for two columns Brendin wrote: I would like to have a table that has a primary key defined on a combination of two columns in the table. In other words a unique key based upon the values in two columns. I don't think I am able to do this in mysql. I think you can only have a primary key on one column and not on a combination of columns. If I am wrong please correct me. At least in 3.23.x (x?) and higher you CAN have a primary key on multiple columns. I am looking for work arounds. I have thought of one. That would be to concatenate the columns and use a field terminator such as a - to separate the values or (columns) in the single column. Then you could use string functions to parse the columns. Ex: [snip] There is a maximum key lenth so if you have two char(255) columns you might need to do something like: ALTER TABLE table ADD PRIMARY KEY( char_col1(100), char_col2(100)); However, this also means that the combination of the first 100 chars from each column must be unique. b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to move db
I'm switching over to a bigger better faster server. My old server is running version 3.23.22-6 mysql on RH and I need to move all the db's to my new box using 3.23.41 installed on RH7.2. So do I just simply move all of the db directories from /var/lib/mysql from one box to the next or are there other files that I need to copy as well. Thanks for the assist - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Natural Sort
In the last episode (Nov 12), Michael Kedl said: Looking thru the MYSQL archives I see a few people had interest in a natural sort method to sort text fields containing numbers. I to would like this feature. Has anything been done for this? Currently sorts text like: 1200 - A tale of 3 dogs 3 bright lights go on Should be: 3 bright lights go on 1200 - A tale of 3 dogs Try ... ORDER BY textfield+0, which will force mysql to convert the field to a number before sorting. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysqladmin help please help
Please help I'm at the end of my rope... I just ran a script to create databases in my SQl server, it runs fine When it completes it says remember to set a password for the mysql root user! I entered this? /usr/local/mysql/bin/mysqladmin -u root -p password password (I want password to be the password) It returns Enter password: I entered password again Is this the password it wants??? Then I want to add this localhost to login into the mysql database I enter: /usr/local/mysql/bin/mysqladmin - u root -h giswinxp -p password password It returns Enter password: I entered password again The error I get is: /usr/local/mysql/bin/mysqladmin: connect to server at 'giswinxp' failed Error: 'Host 'giswinxp.daytonoh.com' is not allowed to connect to this mysql server' Can anyone help me How do I get this server access to this sql server?? I log in as root. Please help me - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php