Strange entry in binary-log (replication temp tables)
Hi, We currently use in one of our projects 3.23.41 with two-way replication as follows: |--| GPW replication |---| | MySQL 1 | -- | MySQL 2 | |GPW Master| |GPW Slave | |GPW1 Slave| GPW1 replication|GPW1 Master| |--| -- |---| GPW and GPW1 are databases with the same structure. my.cnf from MySQL 1: [mysqld] master-host=[ip of 2nd MySQL] master-user=xxx master-password= master-connect-retry=10 replicate-do-db=GPW1 replicate-ignore-table=GPW1.tmp replicate-ignore-table=GPW1.tmp1 replicate-ignore-table=GPW1.tmp2 replicate-ignore-table=GPW1.a replicate-ignore-table=GPW1.b replicate-ignore-table=GPW1.c replicate-ignore-table=GPW1.d replicate-ignore-table=GPW1.e server-id=3 log-bin my.cnf of 2nd MySQL: [mysqld] master-host=[ip of 1st MySQL] master-user= master-password=x master-connect-retry=10 replicate-do-db=GPW replicate-ignore-table=GPW.tmp1 replicate-ignore-table=GPW.tmp replicate-ignore-table=GPW.tmp2 replicate-ignore-table=GPW.a replicate-ignore-table=GPW.c replicate-ignore-table=GPW.b replicate-ignore-table=GPW.d replicate-ignore-table=GPW.e server-id=4 log-bin We are frequently receiving errors in binary log which effect in replication failure: 011017 0:48:50 Slave: did not get the expected error running query from master - expected: 'Got timeout reading communication packets'(1159), got 'no error'(0) 011017 0:48:50 Slave: error running query 'drop table GPW.d' 011017 0:48:50 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'testdb1-bin.020' position 570891011 011017 0:48:50 Slave thread exiting, replication stopped in log 'testdb1-bin.020' at position 570891011 What is the reason of did not get the expected error running query from master - expected: 'Got timeout reading communication packets'(1159), got 'no error'(0) entry? GPW.d is a temporary table made probably by MySQL from query like: Select d.foo as abc, e.foo2 as def from bar d, bar2 e; Maillist archives says that the problem with temporary tables in replication was fixed in 3.23.37 Besides we put replicate-ignore-table=GPW.d entry in my.cnf for both databases. ::Bartek Papierski kierownik oddzialu mailto:[EMAIL PROTECTED] tel. +48 42 631 11 66 mobile +48 609 665 091 ::K2 internet sa o/Lodz http://www.k2.pl 90-418 lodz, al. kosciuszki 1 tel. +48 42 631 11 66 fax +48 42 631 11 66 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Frequently corrupt tables
On Thu, 18 Oct 2001, Kyle Hayes wrote: On Thursday 18 October 2001 09:45, Bill Adams wrote: Matthew Bloch wrote: I'm running several MySQL installation (all version 3.23.37 under Linux) under what I presume are some fairly harsh conditions, and wondered what circumstances cause tables to be corrupted and need fixing with myisamchk. This is happening once every few days and it's becoming a pain. I have a multithreaded process which is constantly opening and closing connections to the database and trying to increase its concurrency until the load average reaches something comfortable like 15, and the network connection is saturated. I've had to throttle it back to stop it opening more than 32 simultaenous DB connections but otherwise it works fine. Until I start getting errors from the table handler, that is, and the whole thing grinds to a halt until I fix the table manually. Can anybody shed some light on this? I can't believe I'm putting it under more load than something like Slashdot would, and they don't (appear to) have half the troubles I've had. I found yesterday (at the advice of this list) that adding an occasional call to FLUSH TABLES fixed my corruption problems. I would do that right before the disconnect or program exit. What kernel are you using? Some of the 2.4 series have... odd... behavior with regards to caching. Happens evenly between three machines: one running 2.2.16-22.0RS (from Redhat 7.0), the others running 2.4.3-12.2RS 2.4.2-2 from Redhat 7.1 so I'm not convinced it's kernel-specific. We're running whatever hardware Rackspace provided us with, not sure, but I think they're all IDE and definitely all running ext2. The only thing that's common is the MySQL version apparently. re: Steve's suggestion, we don't shut the machines down, or at least the corruptions haven't co-incided with the odd reboot we've done. Nor have there been power failures we've been aware of; Rackspace are quite good at telling us about that kind of thing. A few people suggested FLUSH TABLES, but it sounds like a stop-gap, and I didn't realise converting to InnoDB was as simple as stopping, backing up, starting and issuing ALTER TABLE Blah TYPE=INNODB; so I'll probably end up doing that. Nor did I realise that the MySQL version I had on the boxes had InnoDB compiled in, so that sounds like the best solution so far. If it's good enough for Slashdot... In general though, is database corruption really such an occupational hazard to watch for? I was floored that any database might be even occasionally expected to corrupt its data, particularly one that's used so widely as MySQL, but I suppose even with someone like Redhat taking care of compilation, the random combination of kernel database versions might cause some friction. Thanks for the help anyhow, guys, will be migrating to InnoDB ASAP and see if that sorts it out. -- Matthewhttp://www.soup-kitchen.net/ ICQ 19482073 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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 get the correct result -- Thrid Time --
Well, you still have white space in there. Try running the following until it's fixed: UPDATE hotel SET nome_hotel=replace(nome_hotel, ' ', ' ') That will replace any double spaces to one space. If there's still a problem, replace all tabs (\t) and carriage returns (\r) with a space, and repeat the above. And from now on, make sure the data gets put in right :) Steve Meyers -Original Message- From: Adrian D'Costa [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 18, 2001 11:22 PM To: Steve Meyers Cc: DL Neil; Mysql Mailing List Subject: RE: how to get the correct result -- Thrid Time -- On Thu, 18 Oct 2001, Steve Meyers wrote: Trim won't work because the newline is in the middle of the string. Try using the REPLACE function, ie UPDATE hotel SET nome_hotel=replace(nome_hotel, '\n', '') Ok it replaced it. For the second question, it actually doesn't matter if it's CAPS or not. The group by will be done in a case insensitive manner. However, if you really need it lowercase, try this: SELECT LOWER(nome_hotel) FROM hotel GROUP BY 1 The result is mysql SELECT id, LOWER(nome_hotel) FROM hotel GROUP BY 2 limit 10; +--+-+ | id | LOWER(nome_hotel) | +--+-+ | 3825 | | | 3391 | abi d'oru | | 5208 | abou nawas djerba | | 3063 | abou nawas djerba | | 1252 | adams beach | | 9757 | aegean village | | 7973 | aegean | | 8917 | aegean village | | 8122 | aegeon | | 8284 | aeneas | +--+-+ Atain 5208 and 3063 are the same so is 9757 and 8917. I just want only one of them to appear. Thanks Adrian Steve Meyers -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 18, 2001 4:46 AM To: Adrian D'Costa; Mysql Mailing List Subject: Re: how to get the correct result -- Thrid Time -- You didn't appear to answer last time (grump) - reply repeated below. =dn - Original Message - From: Adrian D'Costa [EMAIL PROTECTED] To: Mysql Mailing List [EMAIL PROTECTED] Sent: 18 October 2001 05:34 Subject: how to get the correct result -- Thrid Time -- Hi, I have some records that I need to group by a field. This is easy using group by in the sql statement. What I happening is that I get some data in html format that I have written a script that will extract the data I require an dump it into a table. Everything working fine. The problem is that when the data in entered into the table some fields enter with the new line (\n). So when I use the group by command below is the result. mysql select nome_hotel from hotel group by nome_hotel limit 10; | Abou Nawas Djerba | | Abou Nawas Djerba| | Adams Beach | | Aegean Village | | Aegean | | Aegean Village | This what I don't want since Abou Nawas Djerba and Abou Nawas Djerba are the same. How do I get rid of the space. I tried trim, rtrim nothing works. Second, using the same data I get some of the hotel names in CAPS and I need to convert it to lower and then group by. How do I get these two done? Thanks Adrian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php I have some records that I need to group by a field. This is easy using ... that when the data in entered into the table some fields enter with the new line (\n). So when I use the group by command below is the result. ... Second, using the same data I get some of the hotel names in CAPS and I need to convert it to lower and then group by. How do I get these two done? Adrian, Can you do these (both) at the time the data is first entered into the db? In other words adjust your business rules at the front end, instead of attempting something that's very difficult at the back? =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble
Re: round function
I found different round result at below mysql select round(1.235,2); ++ | round(1.235,2) | ++ | 1.24 | ++ 1 row in set (0.00 sec) mysql select round(1.325,2); ++ | round(1.325,2) | ++ | 1.32 | ++ 1 row in set (0.00 sec) What does it mean?? I wonder that round must increase the front value 1 step, but the second command show the wrong value. Please help me correct round function or any idea to fixed with coding. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) Sommai, May I draw your attention to the list's message footer (immediately above) Here's the text from the relevant part of the manual (~~~/manual_Reference.html#Functions):- - ROUND(X) Returns the argument X, rounded to the nearest integer: mysql select ROUND(-1.23); - -1 mysql select ROUND(-1.58); - -2 mysql select ROUND(1.58); - 2 Note that the behavior of ROUND() when the argument is half way between two integers depends on the C library implementation. Some round to the nearest even number, always up, always down, or always towards zero. If you need one kind of rounding, you should use a well-defined function like TRUNCATE() or FLOOR() instead. ROUND(X,D) Returns the argument X, rounded to a number with D decimals. If D is 0, the result will have no decimal point or fractional part: mysql select ROUND(1.298, 1); - 1.3 mysql select ROUND(1.298, 0); - 1 - Your observation probably has something to do with the rounding of decimal numbers (per 1.325) into binary numbers. In 'the good old days' when you worked right down 'under the hood' of the machine, it was possible to load zero into storage, and then set up a loop adding 0.1 until the value=1.0 - and the loop would never end! [in today's parlance: x=0; while ( x 1.0 ) do x = x + 0.1; ] That's the 'fun part' of approximating decimal calculations in binary! Actually, I haven't tried that ComSc 101-type problem out recently. Does it still happen or our are 'modern tools' sufficiently smart? Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
bug in mysql-test-run script (solaris)
Description: mysql-test-run script uses the test [ -e $BASEDIR/client/.libs/mysqltest ] (line 276 ) since the script uses /bin/sh, and this test [ -e ] is not implemented in /bin/sh on solaris, the mysql-test-run script fails exits How-To-Repeat: run mysql-test-run on solaris Fix: change [ -e ] to [ -r $BASEDIR/client/.libs/mysqltest ] , which checks that the file exists (and is readable) in /bin/sh and /bin/bash alternatively, use [ -x ] (check exists and executable) Submitter-Id: submitter ID Originator: Organization: Peter Lees, Sun Microsystems Inc MySQL support: none Synopsis: mysql-test-run script not compatible with solaris Severity: Priority: Category: mysql Class: Release: mysql-3.23.43 (Source distribution) Environment: System: SunOS ghod 5.8 Generic_108528-07 sun4u sparc SUNW,Ultra-1 Architecture: sun4 Some paths: /usr/perl5/bin/perl /usr/ccs/bin/make /opt/sfw/bin/gmake /opt/sfw/bin/gcc GCC: Reading specs from /opt/sfw/lib/gcc-lib/sparc-sun-solaris2.8/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1761940 Jan 25 2001 /lib/libc.a lrwxrwxrwx 1 root root 11 Sep 26 19:28 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136608 Jan 25 2001 /lib/libc.so.1 -rw-r--r-- 1 root bin 1761940 Jan 25 2001 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Sep 26 19:28 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136608 Jan 25 2001 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/opt/mysql --sysconfdir=/etc/opt/mysql --sharedstatedir=/var/opt/mysql --without-debug --with-innodb --with-unix-socket-path=/var/run/mysql.sock --with-raid --enable-assembler Perl: This is perl, version 5.005_03 built for sun4-solaris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
join tables on UPDATE
hi list,
can mysql allow table joins when using UPDATE query? like
update tranfer left join transferdetails
on transfer.docno = transferdetails.docno
set transferdetails.docno = concat('SJ',transferdetails.DocNo)
where
transferdetails.docno not regexp '^SJ' and
lower(xfrom) = 'san juan';
i get an error
ERROR 1064 at line 1: You have an error in your SQL syntax near 'left join trans
ferdetails on transfer.docno = transferdetails.docno set transfer' at line 1
im using mysql 3.23.41 on win32
TIA
johnhomer
__
www.edsamail.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: innodb problem (with JDBC/transactions)
Erik, run the MySQL server mysqld from a command prompt and do with the mysql client: mysqlcreate table innodb_lock_monitor(a int) type = innodb; (assuming you run a recent version). Then mysqld will print lock information to the standard output and you see what is happening. Regards, Heikki http://www.innodb.com/ibman.html Has anyone encountered locking problems using JDBC with InnoDB tables and transactions? I am wondering if the MySQL JDBC was designed to handle transactions properly since InnoDB is a relatively recent addition to MySQL. The problem I am running into is the 100 locking error, as if the previous transaction hasn't committed. What complicates this is that I am using Bean Managed Persistance (BMP) with container managed transactions with JBoss as my EJB container. This means that I don't actually write the transaction code, so it's hard to tell exactly what's going on. A transaction is generally defined by the container as each method call that the client makes. I have the transaction option set to Required for all the bean's methods, and the 10 error occurs when the client makes a second call for the same table row. An example would be creating a row, then trying to make a second method call to recreate the same row (resubmitting the JSP page.) This should return a duplicate row error, but instead produces a locking error, which the client never sees (shows up on JBoss log.) This makes it seam as though the lock from the original transaction wasn't released, and perhaps the database did not even receive or process the COMMIT. Is anyone having similar problems, and hopefully found a solution? Thanks, Erik - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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 4.0 released
Hi! Michael == Michael Furgal [EMAIL PROTECTED] writes: cut Michael Monty and all: Michael I have re-applied all the Gemini table handler support and Michael ported it to MySQL 4.0. It is too large to send as an email Michael so the patch can be found at: Michael ftp://ftp.nusphere.com/MySQL40/mysql40+gemini-patches.tar.gz Michael There are 2 files in the patch, once for the configuration files Michael and the other for the source code changes. Both need to be Michael applied. Michael The table handler files themselves (ha_gemini.cc and ha_gemini.h) Michael have our (NuSphere) copyright on them, however as it has been Michael mentioned many time previously, these files are based on the Berkeley Michael table handler, so we can hold a shared copyright on the handler files. Michael The remaining changes in support of the Gemini component are made to Michael existing files that MySQL AB already holds the copyright to. Michael You are welcome to the copyright of these changes too (even though I Michael believe you already have it, as we have made no claim to it). Michael The bottom line is that you are welcome to the copyright on the code Michael for all the files/changes in the patch. Thanks! As soon as I get this in writing we will re-apply the patch. Personally I think this email is fine, but our lawyers require us to get this in writing. This is in line with the recommendations by FSF, who also require to get things in writing for larger patches like this. We have actively been communication with the CEO of NuSphere to get this done, but haven't yet got this. I hope we will get this in good time for 4.0.1. Michael Please accept this patch so we can move beyond this issue and continue Michael to have a technical relationship. The business issues between our two Michael companies should not prevent the development and maturing of the Michael excellent MySQL Open Source product. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
GRRRRRRR!!!! Character set
Hi everybody! This is my problem: 1) Environment: OS:sun4u sparc SUNW,Ultra-250 MySQL:3.22.32 2) What I do: I make an excerpt of a table using select lpad(my_field,5,'0') into outfile 'my_file_path' from my_table ... 3) What I would like: I'm in Italy, so in my DB there are characters like è,é,à,ò and many others. I expected that in the file my_file_path I find again è,é,à,ò . 4) What I obtain: In my_file_path, instead of è,é,à,ò, I have something like this: \345, \267, \330 (these values are invented). Please, help me! What can I do? Thanks and best regards, Domenico - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 can I find 3.22.15-Gamma for Solaris?
Hi everybody! I need to download MySQL version 3.22.15-Gamma for Solaris. Does anyone know where I can find it? thank you very much / Johan Johan Brunius DebiTech AB System Developer Stadsgården 6 S-116 45 STOCKHOLM SWEDEN [EMAIL PROTECTED] http://www.debitech.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL 4.0 released
Hi! Britt == Britt Johnston [EMAIL PROTECTED] writes: Britt Now that the patch for Gemini has been created and made Britt available for the new MySQL 4.0 alpha release we are Britt back whole again. I wouldn't really agree with this. Britt I am told that binaries for the complete MySQL 4.0 Britt with support for Gemini tables are now available via Britt mysql.org for everyone to use (MyISAM and InnoDB also). Britt These MySQL server binaries can be used as-is under the Britt GPL without any requirement for a commercial license Britt under any circumstances regardless of the specifics Britt of the application. NuSphere lawyers have said that the GPL is not enforcable, but we don't belive that this true. Because NuSphere has violated the MySQL GPL license, NuSphere has lost all benefits that the GPL gives, like the right to distribute the MySQL server. This means that every download of MySQL from mysql.org and selling of the boxed products from NuSphere that contains MySQL is a violation of the rights of MySQL AB. The GPL also puts restrictions of how a commercial, not open source product can use the code; Any open source/GPL product can use MySQL under the GPL license. If your product that uses MySQL is not open source and you don't want to make it open source, you can't normally use MySQL under GPL but should get MySQL under an normal commercial license from MySQL AB. You can find out more about this at: http://www.mysql.com/doc/U/s/Using_the_MySQL_server_under_a_commercial_license.html Britt As for as 4.0 features you can add the following to Britt the list: Britt o Lock optimization - MySQL executor acquired Britt more locks than required for common queries. Just a note: The extra locks are only relevant for Gemini and BDB tables, not for MyISAM or InnoDB tables. cut Britt BTW, MySQL will be a major topic at the Linux Lunacy Britt Geek Cruise next week. NuSphere is the major sponsor Britt for the event helping make it possible for speakers Britt like Richard Stallman, Eric Raymond and Monty along Britt with myself to further the open source revolution. Britt Drop me a note if you plan on being there! Britt, it's ok to email this list that people can meet you on Linux Lunacy. However, If you have a commercial message that you believe is valuable to our users, then please do as everyone else does: Email the list maintainers and ask for permission to post. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldump - Errcode 24
Hi Environment Mysql ver. 3.23.36 Mysqldump ver. 8.13 Solaris 2.7 Problem I have been trying to use mysqldump --tab to dump a database of 93 records but it always fails on the 61st table with the following error code mysqldump: Can't create/write to file '/bu/fred.sql' (Errcode: 24) (Error code 24 listed as Too many open files) Is there a variable I can set? As far as I can see this is limited to max_allowed_packet and net_buffer_length which seem to have no connection with my problem. Or is this a bug and I'll just have to forget about using --tab? Grateful for any assistance. Regards Karen Birkbeck Natural Resources Institute Chatham, Kent - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: A COMPANY RELEASES A NON-FINAL VERSION OF SOME PRODUCT NON-PREMIUM EDITION
Hi! Jonathan == Jonathan Hilgeman [EMAIL PROTECTED] writes: Jonathan The harm doesn't come from you offering something beneficial to the MySQL Jonathan community. It comes when you abuse the privileges of being on this list and Jonathan send spam. While there may not be anything in stone that says not to send Jonathan commercial software advertisements, you should know better. The only thing Jonathan that keeps spam from filling up the mailing lists is a slight sense of Jonathan ethical marketing behavior, and some people don't have that. cough, cough cut Jonathan, this is as much as my fault as Cathy's. Cathy has asked me previously if it's ok for them to send out one email about Lasso on this email list. I said, that as they are using a legitimate version of MySQL they could do this once to inform the people on the list of their product. We have said ok to things like this a couple of times before to companies that we work with. I am personally against spam, but in some case a one-time directed email to a target group where many could find the product usable can be considered 'ok' as long as things doesn't go out of proportions. I personally don't think that has happened yet. Hope you understand our proportions: We don't want users on this list to feel that they get 'spamed', but once in a while there is a product that we thing is interesting enough for many of our users and we think everyone would benefit if they got some information about this. We plan in the near future to start sending out business news with a short description of new exiting products that works with MySQL. When we get this done, emails like the current one shouldn't be a problem anymore. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
group by clause
Hi, did someone tell me why group by clause not work? I'd like to group volume of order from my customer at the same price and my supplier. here is my code Select custid, symbol, sum(volume) as sumvol, price, mktid, supplier, supplierflag From confirm WHERE mktid=123 or mktid=456 Group by custid, symbol, supplier, supplierflag, price I got this: row#custid symbolsumvol price mktid supplier supplierflag (char) (char)(int)(double) (char) (char)(char) 1 1234 XXX 100 12 123 1C 2 1234 XXX 150 12 123 2C 3 1234 XXX 200 13 123 1E Why row 3 not follow data in row 1 instead of row 2? How could I correct my SQL statement? SF - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Two bugs with complex aggregate functions.
Philip Lijnzaad writes: Yes, it also occurs for mysqld Ver 3.23.43 for pc-linux-gnu on i686. Please try and reproduce it using http://www.ebi.ac.uk/~lijnzaad/mysql/bugreport.tar.gz. Please upload your file to: ftp://support.mysql.com:/pub/mysql/secreat ncftp /pub/mysql put bugreport.tar.gz secreat put bugreport.tar.gz: server said: bugreport.tar.gz: Permission denied on server. (Upload) Cannot open local file secreat for reading: No such file or directory. put secreat: could not open file. ncftp /pub/mysql cd secreat Could not chdir to secreat: server said: secreat: No such file or directory. ncftp /pub/mysql put bugreport.tar.gz put bugreport.tar.gz: server said: bugreport.tar.gz: Permission denied on server. (Upload) ncftp /pub/mysql ls List failed. and resend me your message with exact step on how to repeat the error. Instructions are in the tar file. Philip -- The mail transport agent is not liable for any coffee stains in this message - Philip Lijnzaad, [EMAIL PROTECTED] European Bioinformatics Institute,rm A2-08 +44 (0)1223 49 4639 Wellcome Trust Genome Campus, Hinxton +44 (0)1223 49 4468 (fax) Cambridgeshire CB10 1SD, GREAT BRITAIN - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Two bugs with complex aggregate functions.
Philip Lijnzaad writes: Yes, it also occurs for mysqld Ver 3.23.43 for pc-linux-gnu on i686. Please try and reproduce it using http://www.ebi.ac.uk/~lijnzaad/mysql/bugreport.tar.gz. Philip -- The mail transport agent is not liable for any coffee stains in this message - Philip Lijnzaad, [EMAIL PROTECTED] European Bioinformatics Institute,rm A2-08 +44 (0)1223 49 4639 Wellcome Trust Genome Campus, Hinxton +44 (0)1223 49 4468 (fax) Cambridgeshire CB10 1SD, GREAT BRITAIN Please upload your file to: ftp://support.mysql.com:/pub/mysql/secreat and resend me your message with exact step on how to repeat the error. -- 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
Control Database size
Is there away to control the max size of each database create by user. I mean built into MySQL?? Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 can I find 3.22.15-Gamma for Solaris?
Hi everybody! I need to download MySQL version 3.22.15-Gamma for Solaris. Does anyone know where I can find it? thank you very much / Johan Johan Brunius DebiTech AB System Developer Stadsgården 6 S-116 45 STOCKHOLM SWEDEN [EMAIL PROTECTED] http://www.debitech.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: Two bugs with complex aggregate functions.
Sorry for a typo. Upload your file to this directory : ftp://support.mysql.com:/pub/mysql/secret -- 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
Rotating error log?
Hi! Is there a possibility to rotate mysql error log without stoping the server? TIA -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Uphagena 2, 80-237 Gdansk, tel/fax. (58) 5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqladmin
Hi, I know I'm probably doing something really dumb but has anyone got any ideas why I get the problem below. I cant connect using 'mysqladmin', but using the same user/password can with 'mysql'? The user in question has been set up correctly with grant all from any host. Wouldn't normally be a problem but I have and application that needs to connect via 'mysqladmin'. - [root@freeload mysql]# mysqladmin --user=freeloader --password=abc reload mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'freeloader@localhost' (Using password: YES)' - [root@freeload mysql]# mysql -u freeloader -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 28 to server version: 3.23.22-beta Type 'help' for help. mysql -verifying mysqladmin [root@freeload mysql]# mysqladmin --user=freeloader -p version Enter password: mysqladmin Ver 8.8 Distrib 3.23.22-beta, for redhat-linux-gnu on i386 TCX Datakonsult AB, by Monty Server version 3.23.22-beta Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 1 hour 51 min 41 sec Threads: 1 Questions: 42 Slow queries: 0 Opens: 13 Flush tables: 1 Open tables: 3 Queries per second avg: 0.006 Cheers, m. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Two bugs with complex aggregate functions.
Upload your file to this directory : ftp://support.mysql.com:/pub/mysql/secret Done. -- The mail transport agent is not liable for any coffee stains in this message - Philip Lijnzaad, [EMAIL PROTECTED] European Bioinformatics Institute,rm A2-08 +44 (0)1223 49 4639 Wellcome Trust Genome Campus, Hinxton +44 (0)1223 49 4468 (fax) Cambridgeshire CB10 1SD, GREAT BRITAIN - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqladmin
Sorry for that - Recompiled and everything works as expected. ta. -Original Message- From: Mark Cance [mailto:[EMAIL PROTECTED]] Sent: 19 October 2001 14:07 To: [EMAIL PROTECTED] Subject: mysqladmin Hi, I know I'm probably doing something really dumb but has anyone got any ideas why I get the problem below. I cant connect using 'mysqladmin', but using the same user/password can with 'mysql'? The user in question has been set up correctly with grant all from any host. Wouldn't normally be a problem but I have and application that needs to connect via 'mysqladmin'. - [root@freeload mysql]# mysqladmin --user=freeloader --password=abc reload mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'freeloader@localhost' (Using password: YES)' - [root@freeload mysql]# mysql -u freeloader -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 28 to server version: 3.23.22-beta Type 'help' for help. mysql -verifying mysqladmin [root@freeload mysql]# mysqladmin --user=freeloader -p version Enter password: mysqladmin Ver 8.8 Distrib 3.23.22-beta, for redhat-linux-gnu on i386 TCX Datakonsult AB, by Monty Server version 3.23.22-beta Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 1 hour 51 min 41 sec Threads: 1 Questions: 42 Slow queries: 0 Opens: 13 Flush tables: 1 Open tables: 3 Queries per second avg: 0.006 Cheers, m. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to get the correct result -- Thrid Time --
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sorry, I forgot to answer your question about similar rows. Rows 5208 and 3063 are NOT the same (row 5208 has spaces and returns), besides your query asked for ALL rows not just the unique ones. Remember, computers only give you what you asked for, not what you want. If you want to get only unique, non-blank results then SELECT DISTINCT replace(replace(nome_hotel,\n,), , ) from hotel WHERE nome_hotel NOT NULL or trim(nome_hotel) NOT LIKE order by nome_hotel limit 10; On Fri, 19 Oct 2001, Adrian D'Costa wrote: | Date: Fri, 19 Oct 2001 10:43:31 +0530 (IST) | From: Adrian D'Costa [EMAIL PROTECTED] | To: Clyde Jones [EMAIL PROTECTED] | Subject: Re: how to get the correct result -- Thrid Time -- | | Hi, | | Thanks. It gave an error but it work after adding the \n. Now the | result is: | | +--+-+ | | id | replace(nome_hotel,\n,) | | +--+-+ | | 3825 | | | | 3827 | | | | 3391 | Abi d'Oru | | | 5208 | Abou Nawas Djerba | | | 3063 | Abou Nawas Djerba | | | 1252 | Adams Beach | | | 9757 | Aegean Village | | | 7973 | Aegean | | | 8917 | Aegean Village | | | 8122 | Aegeon | | +--+-+ | | If you notice id 5208 and 3063 are the same so also with 9757 and 7973. I | tried using trim it is the same. Any pointers. | | Thanks | | Adrian | | On Thu, 18 Oct 2001, Clyde Jones wrote: | | the doc page is here | http://www.mysql.com/doc/S/t/String_functions.html | try | | select replace(nome_hotel,\n,) | from hotel | group by nome_hotel limit 10; | - -- They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety. - Benjamin Franklin Clyde Jones - http://www.clydec.net -BEGIN PGP SIGNATURE- Version: PGP 6.5.8 iQA/AwUBO9At+0P80ESqUED/EQICnwCgvVdsWfN0Nr4/HnRoa7/5UKCuYggAoOB1 g4svPHDm8QFnMXAAdE2RDYce =ONgB -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to get the correct result -- Thrid Time --
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 OK it looks like you have gotten rid of your excess carriage returns, but now you need to remove the excess spaces. did you try nesting the functions? try this select replace(replace(nome_hotel,\n,), , ) as nome d'hotel from hotel group by nome_hotel limit 10; you can update your entire database and remove the excess spaces and returns by doing the following update hotel set nome_hotel to replace(replace(nome_hotel,\n,), , ) HTH On Fri, 19 Oct 2001, Adrian D'Costa wrote: | Date: Fri, 19 Oct 2001 10:43:31 +0530 (IST) | From: Adrian D'Costa [EMAIL PROTECTED] | To: Clyde Jones [EMAIL PROTECTED] | Subject: Re: how to get the correct result -- Thrid Time -- | | Hi, | | Thanks. It gave an error but it work after adding the \n. Now the | result is: | | +--+-+ | | id | replace(nome_hotel,\n,) | | +--+-+ | | 3825 | | | | 3827 | | | | 3391 | Abi d'Oru | | | 5208 | Abou Nawas Djerba | | | 3063 | Abou Nawas Djerba | | | 1252 | Adams Beach | | | 9757 | Aegean Village | | | 7973 | Aegean | | | 8917 | Aegean Village | | | 8122 | Aegeon | | +--+-+ | | If you notice id 5208 and 3063 are the same so also with 9757 and 7973. I | tried using trim it is the same. Any pointers. | | Thanks | | Adrian | | On Thu, 18 Oct 2001, Clyde Jones wrote: | | the doc page is here | http://www.mysql.com/doc/S/t/String_functions.html | | try | | select replace(nome_hotel,\n,) | from hotel | group by nome_hotel limit 10; | | On Thu, 18 Oct 2001, Adrian D'Costa wrote: | | | Date: Thu, 18 Oct 2001 10:04:03 +0530 (IST) | | From: Adrian D'Costa [EMAIL PROTECTED] | | To: Mysql Mailing List [EMAIL PROTECTED] | | Subject: how to get the correct result -- Thrid Time -- | | | | Hi, | | | | I have some records that I need to group by a field. This is easy using | | group by in the sql statement. What I happening is that I get some data | | in html format that I have written a script that will extract the data I | | require an dump it into a table. Everything working fine. The problem is | | that when the data in entered into the table some fields enter with the | | new line (\n). So when I use the group by command below is the result. | | | | mysql select nome_hotel from hotel group by nome_hotel limit 10; | | | Abou | | Nawas Djerba | | | | Abou Nawas Djerba| | | | Adams Beach | | | | Aegean | | Village | | | | Aegean | | | | Aegean Village | | | | | This what I don't want since Abou | | Nawas Djerba and Abou Nawas Djerba are the | | same. How do I get rid of the space. I tried trim, rtrim nothing works. | | | | Second, using the same data I get some of the hotel names in CAPS and I | | need to convert it to lower and then group by. How do I get these two | | done? | | | | Thanks | | | | Adrian | | | | | - -- They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety. - Benjamin Franklin Clyde Jones - http://www.clydec.net -BEGIN PGP SIGNATURE- Version: PGP 6.5.8 iQA/AwUBO9ArzkP80ESqUED/EQJ3FwCgoFMh4HgKtwNlgwNK0IflH5VHkkAAn0HP pO6A3kYZgTTUN/uFgz54J57A =TgdS -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql db character comparisons
I've got a mysql database with unique index on a column defined as varchar, and character = set=latin1. But, if I insert, for example, 'protege' into the varchar column, then try to insert 'protegé', I get a 'duplicate entry' error. Mysql seems to be treating 'e' and 'é' as the same character. Is there a way to make them compare differently? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Two bugs with complex aggregate functions.
OK, I have got a first one. I will try it out as soon as I find some time ... -- 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
MySQLGUI problem
MySQLGUI-1.7.5 was working. Now, when I start it and put the mouse pointer over it, it disappears. Reinstalled only to get the same thing. Any ideas? cedric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: /usr/local/mysql/var/ bin files
Hi, Just stopped and started mysql with the -O max_binlog_cache_size=100, but still this bin.001 file is increasing way over this limit, so it may not be this:( I didn't think it was due to replication as we do not have any replication setup. Also check the contents of the file. ?bin(iI;E3.23.43-log(iI;iI;ADatabasecreate table tablename(date varchar(9), time varchar(10), orig varchar(20), action varchar(10), ifdi r varchar(8), src varchar(30), dst varchar(20), service varchar(20))FiI;aDatabaseINSERT INTO table this is the top, the all INSERTS and values added are in the file. I assumed it was a cache as these were all previous actions but can't get a limit on this file. It is okay to periodically delete this file as it does not cause any damage to the database, already checked that one:) but above option does not seem to work. anyone have any other ideas thanks :) Leon. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 again
Hi there,
I'm trying to use fulltext for speeding up my queries, but i found some
strange
difference in the results between fulltext Like ; for example:
AGAINST('+shed +some +light +on +this')
gives less matches then:
AND field LIKE '%shed%' AND field LIKE '%some%' AND field LIKE '%light%'
AND field LIKE '%on%' AND field LIKE '%this%'
The query itself is 10 times faster when I use fulltext. So I would
really like to use that ;)
You would think it would give the same results right? I have configured
MySQL to not index word less then 2 characters,
instead of the standard 4 characters. The fulltext index is up-to-date.
What could be the problem?
Thanks,
Jeroen
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Raid support in windows 2k
I downloaded MySql v3.23.43 binaries to see if I could make use of the system for my database 'stuff'. I have several tables that when loaded will be 2gb and the concept of raid support would be appealing. Raid does not seem to be enabled on the default download. Because of this I downloaded the source files and attempted to turn Raid on. I went thought the code and changed: /* Use MySQL RAID */ /* #undef USE_RAID */ to /* Use MySQL RAID */ #define USE_RAID 1 I made this change based on the other options that were turned on. I the rebuilt with MS VC++ 6.0 Enterprise. But when I run mysqld-nt.exe Raid is still not enabled. What have I done wrong? Can anyone help? Thank you. Otto Bean Informative Software Computer [EMAIL PROTECTED] PH: (856) 858-5135 FX: (856) 858-2224 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQLGUI problem
cedric writes: MySQLGUI-1.7.5 was working. Now, when I start it and put the mouse pointer over it, it disappears. Reinstalled only to get the same thing. Any ideas? cedric What operating system do you use ?? Have you changed X version or window manager ?? I never heard of something like that. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fulltext again
Hi!
On Oct 19, Jeroen Geusebroek wrote:
Hi there,
I'm trying to use fulltext for speeding up my queries, but i found some
strange
difference in the results between fulltext Like ; for example:
AGAINST('+shed +some +light +on +this')
gives less matches then:
AND field LIKE '%shed%' AND field LIKE '%some%' AND field LIKE '%light%'
AND field LIKE '%on%' AND field LIKE '%this%'
The query itself is 10 times faster when I use fulltext. So I would
really like to use that ;)
May be because your LIKE query finds also
'something will not shed any light on this!!!'
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: MySQLGUI problem
I have found the MySQL Manager to be incredibly buggy and a pain to even do the most simplest things. I would suggest that you try MySQL-Front http://www.anse.de/mysqlfront/ Very easy to use and very functional. - Original Message - From: Sinisa Milivojevic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, October 19, 2001 10:28 AM Subject: Re: MySQLGUI problem cedric writes: MySQLGUI-1.7.5 was working. Now, when I start it and put the mouse pointer over it, it disappears. Reinstalled only to get the same thing. Any ideas? cedric What operating system do you use ?? Have you changed X version or window manager ?? I never heard of something like that. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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 again
Hi, May be because your LIKE query finds also 'something will not shed any light on this!!!' That's what i though just after posting this to the list. When is the phrase support planned to be inserted in MySQL 4? That would be a very nice feature! Regards, Jeroen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Frequently corrupt tables
Spoiler: You may be right about the bad libs... Kyle Hayes wrote: On Thursday 18 October 2001 12:31, Bill Adams wrote: Hmm, 2.2 doesn't do SMP really well. However, its drawbacks are limited to underuse of the CPUs rather than any kind of corruption or other issue. You would get much better performance with 2.4, but 2.2 is probably a little more stable. 2.4 is not an option for me because: o Right not I use Informix as my production database. Until they officially support 2.4 or I 'upgrade' to MySQL I am stuck in the 2.2.x series. o Until the VM crap is worked out, I am not installing the 2.5, er. 2.4 kernels on any production machines unless it comes with the distribution. Is this a DAC960 or something similar? If so, make sure you have the absolute latest drivers. We have some dual processor machines with those controllers (or something closely related) and had to do many driver updates before it stabilized. And, we're still not totally convinced. If this is a big SCSI RAID card, I would definitely check the drivers and make sure that there isn't something newer/more stable out there. I have a Mylex DAC1164P for the /, /home, etc. using RAID5. All of the MySQL tables are on an Adaptec AIC-7899 Ultra 160/m SCSI host adapter which is a dual channel UW controller. Statistics: (scsi0:0:0:0) Device using Wide/Sync transfers at 80.0 MByte/sec, offset 31 Transinfo settings: current(10/31/1/0), goal(10/127/1/0), user(9/127/1/2) Total transfers 36738885 (18761976 reads and 17976909 writes) Waiter! I'll have two of what that gentleman over there is having. :) What filesystem are you running? ext2. At least that is what linux sees. The disks are actually hardware raid0 winchester flashdisks. Flash? I.e. these are solid state disks? If that is true, then maybe that is part of the problem. Flash is different from normal disk. No, that is the product name. http://www.winsys.com/products/ Basically, it is a box with 12 drives in it and a dual channel scsi controller (in my model). As far as Linux is concerned, each box appears as two very large, very fast drives on two channels. You can partition in different ways and get them with one channel, etc.. Can these disks correct for bad sectors? If so, the usual method to force remapping of bad sectors is to use dd: AFAIK, the flash controller corrects for that. But then again I am running RAID0 and winchester systems does not officially support that level (they do 0+1, 5, others) because part of what they are selling besides blazingly fast raid boxes is data security and integrity. Obviously you do not get that with RAID0. For my application that is not an issue. I care only about speed and volume: my raw data is backed up elsewhere. But I digress dd if=/dev/zero of=/dev/XXX bs=1M count=YYY Where XXX is the RAID device and YYY is the number of megabytes of storage. Please make a backup of your data first :-) On a normal disk, this causes a write to each sector on the whole drive. That in turn causes the firmware on the drive to remap any bad sectors found this way. If your disks support this, you might be unpleasantly surprized how many problems go away after this. Most newer drives do this automatically, but it can still trash your data. By doing the line above, you force the issue before you have valid data on the disk. I in my case I just 'login' to the controller on the flashdisk to get statistics such as bad sectors and such. Not to sound too much like an advertisement for Winchester Systems but these people have been around for a long time and the controllers I have have been too and are well tested and used by many other companies/people with much more critical needs than I have. I also do not have problems with the Informix tables on the same disks using the same dataloader under the same conditions. And it happens on different enclosures/disks/etc.. We've done 7M rows in one single input file (just a hair under the 2GB limit for the older ext2 filesystem we have on that particular machine). No problems at all. That was with MySQL 3.23.26 or something close to that. We've done tests much larger than this that were either driver via Perl and DBI, or from a flat file. Well, I am running an ancient version of DBI. I will upgrade to a more modern version of DBI and msql-mysql-modules; reload data; and report back. Is the data getting mangled or the index? If myisamchk can fix the problem, That is the funny thing, I had to do a mysqldump file; mysql file to fix the table. myisamchk would report the table was bad, I would try to repair with -o (and just about every other level). then myisamchk would report it was good (even with -e). When I continued to load the data, it would quickly become corrupted again. Even rebuilding all of the indexes would not fix it. Running the mysqldump, mysql fixed it much better.
RE: join tables on UPDATE
That will be in some release of either 4.0 or 4.1, I'm not sure which.
Steve Meyers
-Original Message-
From: JohnHomer [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 2:11 AM
To: [EMAIL PROTECTED]
Subject: join tables on UPDATE
hi list,
can mysql allow table joins when using UPDATE query? like
update tranfer left join transferdetails
on transfer.docno = transferdetails.docno
set transferdetails.docno = concat('SJ',transferdetails.DocNo)
where
transferdetails.docno not regexp '^SJ' and
lower(xfrom) = 'san juan';
i get an error
ERROR 1064 at line 1: You have an error in your SQL syntax near
'left join trans ferdetails on transfer.docno =
transferdetails.docno set transfer' at line 1
im using mysql 3.23.41 on win32
TIA
johnhomer
__
www.edsamail.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: join tables on UPDATE
JohnHomer wrote:
can mysql allow table joins when using UPDATE query? like
The manual answers these questions.
update tranfer left join transferdetails
on transfer.docno = transferdetails.docno
set transferdetails.docno = concat('SJ',transferdetails.DocNo)
where
transferdetails.docno not regexp '^SJ' and
lower(xfrom) = 'san juan';
i get an error
ERROR 1064 at line 1: You have an error in your SQL syntax near 'left join trans
ferdetails on transfer.docno = transferdetails.docno set transfer' at line 1
im using mysql 3.23.41 on win32
The manual states clearly that 3.23 does not support joins with UPDATE. And RTM to
find out if 4.0 does.
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 again
Hi! On Oct 19, Jeroen Geusebroek wrote: Hi, May be because your LIKE query finds also 'something will not shed any light on this!!!' That's what i though just after posting this to the list. When is the phrase support planned to be inserted in MySQL 4? No specific deadlines yet - but as soon as basic boolean search functionality will be done. 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 server doesn't run
Mark Coldheart wrote: I've a little bit problem about running mysqld server. I've run /etc/rc.d/init.d/mysqld start it will appear mysqld dead but subsys locked. But if i stop the service, it will fail. It is also happened when i kill the service. Mm, redhat question. Make sure mysql is not running: ps ax |grep mysql If it is not, the lock file is in /var/lock/subsys. Remove the mysql one and start it. The other problem is when i run mysql it will appear can't connect to Mysql server through socked /var/lib/mysql/mysql.sock(111) What i must do now ??? Which part that i have to configure ? Without a running mysqld, you cannot connect. 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
problem with starting mysql server + segmentation fault
hi, I have installed mysql3.23.39 on solaris2.8. but,when I try to run the server, I am getting segmentation fault.can anybody help me..? I have followed instructions as given in the php manual. shell groupadd mysql shell useradd -g mysql mysql shell cd /usr/local shell gunzip mysql-3.23.39-pc-solaris2.8-i386.tar.gz shell./gtar-i386 xvf mysql-3.23.39-pc-solaris2.8-i386.tar shell ln -s mysql-3.23.39-pc-solaris2.8-i386 mysql shell cd mysql shell scripts/mysql_install_db shell chown -R root /usr/local/mysql shell chown -R mysql /usr/local/mysql/data shell chgrp -R mysql /usr/local/mysql shell chown -R root /usr/local/mysql/bin shell bin/safe_mysqld --user=mysql 9800 shell Starting mysqld daemon with databases from /usr/local/mysql-3.23.39-pc-solaris2.8-i386/data Segmentation Fault - core dumped 011019 17:52:28 mysqld ended regards prasad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 alpha source build with pgcc
Hi, Has anyone been able to build MySQL 4.0 source using pgcc? I tried for several hours with different setting in each try without any success. MySQL 4.0 alpha does compile, but it refuses to start, dumping the same error output each time such attempt is made: *** Being error output *** InnoDB: The first specified data file /usr/local/mysql/data/ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file /usr/local/mysql/data/ibdata1 size to 67108864 InnoDB: Database physically writes the file full: wait... InnoDB: Log file /usr/local/mysql/data/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /usr/local/mysql/data/ib_logfile0 size to 5242880 InnoDB: Log file /usr/local/mysql/data/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /usr/local/mysql/data/ib_logfile1 size to 5242880 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: creating foreign key constraint system tables InnoDB: Assertion failure in thread 1024 in file dict0crea.c line 1115 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16773120 record_buffer=131072 sort_buffer=2097144 max_used_connections=0 max_connections=1000 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 2192372 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffe7f8, stack_bottom=0x776f6c6c, thread_stack=65536, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x615f7861 is invalid pointer thd-thread_id=-185339151 *** End of error out put *** I found a message by Gilles in the list stating exactly the same problem. Is this a known bug or are there several compiler setting I should pay attention to? The above error disappears if I compile the source with gcc. Build environment is of following: OS: Red Hat 7.1 (kernel 2.4.3-12) Compiler: pgcc-2.95.2 19991024 (release) Compiler setting: CFLAGS=-O3 -mpentium -mstack-align-double CXX=gcc CXXFLAGS=-O3 -mpentium -mstack-align-double -felide-constructors -fno-exceptions -fno-rtti configure setting: --prefix=/usr/local/mysql-4.0.0-alpha-source --disable-shared --enable-assembler --with-extra-charsets=none --with-mysqld-ldflags=-a ll-static --with-openssl --with-innodb --without-debug Regards, Jindo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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.sock
cedric wrote: /usr/bin/mysql start returns the error message: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock. It was there, but now it's not. That is the wrong command, use either safe_mysqld or mysqld_safe. Or possibly /etc/rc.d/init.d/mysql start Also, all the files in /var/lib/mysql now have green question marks over them. Uh, It sounds like your file system is corrupt. The did not before. I was able to open *.err and read it. '/usr/bin/mysql_install_db' did not change anything. Of course not, that only boot straps the db. 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
JDBC: Losing precision when mapping doubles from Java
Hello, I'm losing precision when I send a double to a mysql database from a Java program trough JDBC. Code: stmt.setDouble(i+3,((Double)value).doubleValue()); I use a prepared statement. Now, if I print out the double in java before sending it to the MySql database this is what it looks like: 1277.00143924408 (11 decimal digits) When I look in the database, this is what the value looks like: 1277.00143924 (8 decimal digits) The type of the column is double(without any precision or digits specification). Now the big question is: what happened to the last 3 decimal digits? Or is this just a display problem? Does the database only display 8 decimal digits by default? So I changed the type in the table to double(40,20) and now the value in the database changes to: 1277.0014392408155417 (exactly 20 decimal digits) Ok, next I set the type to double(40,11) and I get: 1277.00143924000 (11 decimal digits). Anyway, I lose the last digits. WHY? I thought that the MySql double type has the same precision than the java double type? Whats wrong here? Thanks for any answers... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 big for max_allowed_packet - service problem or JDBC driver problem?
I'm developing a Java application that uses a pool of JDBC connections to store and retrieve data in a MySQL database. I'm using the 3.23.38 Win32 version of MySQL, on Windows 2000 Professional, and the mm.mysql-2.0.4 JDBC driver. I'm using a table with a MEDIUMTEXT field, which the MySQL documentation says can handle up to 16.7 Megs of data. However, I've run into problems with this Exception being thrown (the [java] prefix is there because I'm running this from inside of an ant script): [java] java.sql.SQLException: Error during query: Unexpected Exception: java.lang.IllegalArgumentException message given: Packet is larger than max_allowed_packet from server configuration of 65536 bytes [java] at org.gjt.mm.mysql.Connection.execSQL(Connection.java:898) [java] at org.gjt.mm.mysql.Connection.execSQL(Connection.java:815) [java] at org.gjt.mm.mysql.Statement.executeUpdate(Statement.java:227) [java] at org.gjt.mm.mysql.jdbc2.Statement.executeUpdate(Statement.java:97) [java] at com.incellico.arrayex.data.MyClass.storeBigField(MyClass.java:xxx) I'm running mysql as a service on my machine, using the mysqld-max-nt executable. When I take a look at the variables, the output reports that the max_allowed_packet size is set to 1 Meg. I've tried explicitly setting max_allowed_packet in the C:\WINNT\my.ini file, by adding a [mysqld] section, and I've tried bumping up the available memory by adding a [safe_mysqld] section with ulimit -d 256000, as was suggested in the MySQL manual. Neither has worked. Could this be a problem with the mm.mysql JDBC driver itself? Peter Bailey (work) [EMAIL PROTECTED] (home) [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible bug in self-join order optimization
Well, answering my own email, what I thought was a bug is not one at all. I was mistaken in thinking that MySQL paid any attention to the WHERE conditions when optimizing the join order beyond determining which keys are used for the join, correct? This is really terrible for queries like mine where the query could be sped up by orders of magnitude if the join optimizer would just determine which table in the join to scan and which to do the key lookup on based on a more intelligent estimation of the number of rows from each table. It would have to go beyond looking at what keys are used in the join (since each of the tables in my query can be looked up by the same key) and account for the WHERE conditions placed on the tables in the join. Is there sufficient metadata to estimate rows coming from a table based on conditions placed on the attributes of that table? Where is it? Has anyone ever thought of coding this? Can anyone give me a place to start? eric. On Thu, Oct 18, 2001 at 10:40:43AM -0500, Eric wrote: I am sending this again as I am desperate for some help and believe this to be a signifigant bug if it actually is one...which it seems to be. See below for examples. What is quite puzzling is MySQL's estimation of the number of rows from each of the self-joins. The conditions on alias queryTable0 actually refer to 1582 rows, and the conditions on alias queryTable1 refer to 39 rows. Notice in the EXPLAIN below that when I flip around the join order, MySQL thinks that 1152 (which is its estimation for 1582) rows are coming from queryTable1, whereas with the original join order, it thought 1152 rows were coming from queryTable0...this seems like a bug to me since the conditions on those two aliases are the same between the two queries. Only the FROM index queryTable0, index queryTable1 is flipped to FROM index queryTable1, index queryTable0. SELECT DISTINCT queryTable0.num, queryTable0.value, queryTable1.value FROM index queryTable0, index queryTable1 WHERE queryTable0.path=24 AND queryTable0.type=E AND queryTable1.path=27 AND queryTable1.type=E AND queryTable0.num=queryTable1.num AND queryTable0.nvalue 0.0 AND queryTable0.nvalue = 90.0 AND queryTable1.nvalue 140.0 AND queryTable1.nvalue = 200.0; +-+--+--++-+++--+-+ | table | type | possible_keys| key| key_len | ref| rows | Extra | +-+--+--++-+++--+-+ | queryTable0 | ref | pathndx,numndx | pathndx | 4 | const | 1152 | where used; Using temporary | | queryTable1 | ref | pathndx,numndx | numndx | 4 | queryTable0.num | 53 | where used | +-+--+--++-+++--+-+ 2 rows in set (0.01 sec) On Wed, Oct 17, 2001 at 04:04:21PM +0300, Michael Widenius wrote: We have done some modifications to optimizer in 4.0, but nothing that should affect this. What is the output from EXPLAIN if you swap the tables ? EXPLAIN of query with FROM index queryTable1, index queryTable0: +-+--+--++-++--+-+ | table | type | possible_keys| key| key_len | ref| rows | Extra | +-+--+--++-++--+-+ | queryTable1 | ref | pathndx,numndx | pathndx | 4 | const | 1152 | where used; Using temporary | | queryTable0 | ref | pathndx,numndx | numndx | 4 | queryTable1.num | 53 | where used | +-+--+--++-++--+-+ 2 rows in set (0.01 sec) What is the output from show create table 'index' CREATE TABLE is: CREATE TABLE `index` ( `indexnum` int(10) unsigned NOT NULL auto_increment, `parent` int(10) unsigned NOT NULL default '0', `path` int(10) unsigned NOT NULL default '0', `type` char(1) NOT NULL default '', `tagname` int(10) unsigned NOT NULL default '0', `atrname` int(10) unsigned NOT NULL default '0', `num` int(10) unsigned NOT NULL default '0', `nvalue` double default NULL, `value` mediumtext, PRIMARY KEY (`indexnum`), KEY `parentndx`(`parent`), KEY `pathndx`(`path`), KEY `tagnamendx`(`tagname`), KEY `atrnamendx`(`atrname`), KEY `numndx`(`num`), ) TYPE=MyISAM MAX_ROWS=315360 PACK_KEYS=1 -- _ _ | |(_) http://ir.iit.edu/~ej | _| | | Page me via ICQ at | |___ | |
more problems with Wokrbench migration tool
Hi.
I have ran into couple of problems using Oracle workbench migration tool.
I was wondering if anyone out there has any insight.
1. If a date field in a mysql table has a default (let's say -12-31)
the script for oracle table will also have -12-31 as a default field.
This doesn't seem to work in Oracle because Oracle expects to see
something like to_date('-12-31','-mm-dd'). I couldn't find a way
to modify that in the Workbench.
2. If a numeric field is defined in the mysql database as smallint,
the workbench creates a corresponding Oracle field as num(250),
while oracle would expect the presicion no more than 38. Again,
I couldn't find a way to fix it in the workbench.
3. If I'm attempting to modify a oracle field's parameters in the workbench,
it won't let me to move to another table without asking do you want
to save the changes and won't move on unless I click on No.
I would appreciate any comments on these issues
tia
AOD
__
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Installation
Dear Sir, Please do help me. I am trying to install the mysql-3.23.42 on my FreeBSD4.3. However, the mysql cannot interface with the BerkeleyDB3.1.17. An error message come up the Version 3.2.9 is need, but when I used the 2BerkeleyDB3.2.9 , the error messsage come up again. Please advise. Yours sincerely Stanley - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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 4.0 released
Hi! First I have to apologize to the email list for this discussion: It certainly doesn't belong here. On the other hand, as the creator of MySQL and one who has personally sued by NuSphere corporation I can't let things like this go unanswered. Britt == Britt Johnston [EMAIL PROTECTED] writes: Britt NuSphere is a strong believer in the GPL. Our history Britt and the 150,000 lines of code we have licensed under the Britt GPL reinforce that fact. Britt http://nusphere.com/releases/062800b.htm - June 28, 2000 Britt http://nusphere.com/releases/013001.htm - January, 30, 2001 Britt Please feel free to contact me directly if you would like Britt to discuss this further. Britt, this is not the right forum to discuss things like this. You can't go around the fact that NuSphere violated the MySQL GPL license by distributing MySQL staticly linked with Gemini without giving the users access to all the source code. You also forbid anyone to use this distribution without having a license from NuSphere. On your website you even had the following explanation for this: -- Q: Can I copy NuSphere's products freely and distribute them? A: No, NuSphere's software products are all proprietary, copyrighted works with licenses that limit distribution. They cannot be freely copied. The download and CDROM versions of our products, including NuSphere MySQL and the entire Advantage product line, cannot be freely copied. Q: I think all software should be completely free - why isn't NuSphere's? A: NuSphere believes all software cannot be completely free. We contribute to Open Source projects that provide free versions of their software. Individuals who do not wish to pay to use specific software are encouraged to join those communities. -- NuSphere also didn't release Gemini under GPL until MySQL AB sued them about violating the GPL license and misusing our trademark. Releasing Gemini under GPL doesn't however cure the GPL violation, (see http://www.gnu.org/copyleft/gpl-faq.html). Until all holders of any of the violated GPL code has given NuSphere forgiveness, they have lost all the rights that GPL gives them to the violated code. Britt, If you want discuss this, you know how to reach me. I am, as always, ready to discuss ways to solve our differences. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Database broken
I had one mysql database established on my development server. A new project came along so I created a new database. The new database works fine, but the first database doesn't work any more. When I try to access data from the database, either from JDBC or by starting mysql I get this error: Didn't find any fields in table 'comments' I get this for all tables. SHOW TABLES displays all the tables. CHECK TABLE says Can't find file: './avalon/comments.frm'. When mysql starts up it says Starting mysqld daemon with databases from /mysql/data which is the correct path. The files for the first database haven't been moved or renamed. If I were starting mysql from the wrong directory I would think that neither database would work. Can someone tell me what I've done wrong? -Sean Hammon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Unix Shell Auth from MySQL
checkout pam_mysql under the ports collection in fBSD...;) cheers, Mikel Chris Aitken wrote: Hi, Something ive been thinking about for a while but havent been able to come up with a definitive answer is this... Is there a system out there which can take over from the normal (unix based... FreeBSD/Linux type) telnetd method of authenticating a login off /etc/master.passwd and instead using a MySQL database for auth. It sounds like a bit of a pipe dream but I figured i'd ask here because I havent been able to find anything on this. Thanks Chris -- Chris Aitken - Administration/Database Designer - IDEAL Internet email: [EMAIL PROTECTED] phone: +61 2 4628 fax: +61 2 4628 8890 __-__ # ln -s /dev/null /bin/laden If you don't like my typing, all flames to /bin/laden - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL 4.0 released
Britt o Lock optimization - MySQL executor acquired Britt more locks than required for common queries. Just a note: The extra locks are only relevant for Gemini and BDB tables, not for MyISAM or InnoDB tables. The extra locks issue does apply to InnoDB for update statements and select for update statements. Of course it does not apply to MyISAM since MyISAM has neither row level locking or transactions. -regards Richard Tuckerhttp://www.nusphere.com Gemini team member - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Server Problems
You may want to check your /tmp partion. it may be creating some temp tables. -t, --tmpdir=path Path for temporary files. It may be useful if your default /tmp directory resides on a partition too small to hold temporary tables. Byron [EMAIL PROTECTED] wrote: Hello All, Background: -Site has 8000 uniques per day, average of 300 online at once hammering the forums. -POST table has over 350,000 entries When doing a backup with mysqldump and then trying to re-import the data to a test server, we keep getting: ERROR 1030 at line 207477: Got error 28 from table handler - No space left on device Is there an way to remedy this problem? Obviously we have a setup error or something as there is a 20gig drive in the mySQL server. mysql database Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
indexing question
Can anyone give me some tips on how indexes work? I noticed that UNIQUE() seems to create a lock on all given fields per call like UNIQUE (md5, mime) so that no row can have the same combination of md5 and mime type which is good but assuming I wanted to have each unique on it's own I'd need UNIQUE (md5), UNIQUE (path) right? Are INDEX's the same as KEY's? When I INDEX() something it seems to dump as a KEY(). Does it matter if you INDEX ( path, md5, mime ) or INDEX(path), INDEX(md5), INDEX(mime)? They seem to look different in the db depending how I do it but both seem to run at about the same speed even over a large db. Either is a huge speed increase over the original that had only primary keys it seems. Right now in a db with several tables.. some with hundreds of thousands of rows.. the average query time seems to be about half a second to a second on a P133. Is that good, bad, or normal? *^*^*^* Michael McGlothlin [EMAIL PROTECTED] http://mlug.missouri.edu/~mogmios/projects/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
slow server
Hello, I'm running the following Redhat Linux IASP 2.0 mySQL If my site is continually viewed the iASP engine or mySQL seems to slow down Any ideas on why or how to correct this problem Thanks Neil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Where are the best Access -- Mysql FAQs
Brand new Newbie here! My first post of any meaning! Considering the futile exercise of building an Access frontend to MySql. Where are the best FAQs, Books, Websites? Thanks howard gramer Home of NYPD and FDNY Environment..: Telecommunications at the circuit level. Email: [EMAIL PROTECTED]Flower: 124096153 MSN Messenger: howard [EMAIL PROTECTED] All communications from me are grounded in opinion. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The opposite of where column_name LIKE %string%
On Wed, Oct 17, 2001 at 06:25:49PM +0200, Ciprian A. wrote: I need to select from a table all the records that do not contain a certain string. Any idea how I can do this? You mean like SELECT * FROM TABLE WHERE FIELD NOT LIKE %stuff%? :) -- Michael T. Babcock http;//www.fibrespeed.net/~mbabcock/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MYSQL 4.0 bug with fulltext (case change) updates
Hi,
I think this is a bug. The script to recreate the problem is included
below. This problem appears consistently as long as there's a fulltext
index and a regular index on the same field and you do an update to
change the case of a single char. It doesn't matter if the fulltext
index includes other fields. If the regular index is removed, then after
the insert (where the case of one char in a field is changed) the table
is briefly marked as crashed, and then seems to automagically fix itself
(which is worrying).
The bug also occurs when you change the case of a single char in a
varchar field and add a word to the sentence e.g.:
'experience with c required' changes to 'experience with C is required'
also causes the problem.
Thanks as always,
~mark
ps: If anyone knows of a workaround please let me know. thanks.
__BEGIN__
#This works fine on mysql 3 but crashes the table on 4 alpha
CREATE TABLE tester (
id int(11) auto_increment,
title varchar(100) default '',
PRIMARY KEY (id),
#If you comment out the following index, then the table is briefly
marked as crashed
# but appears to recover.
KEY ind5 (title),
#The fulltext index appears to be the root of this prob.
FULLTEXT KEY FT1 (title)
) TYPE=MyISAM;
insert into tester (title) values ('this is a test');
update tester set title='this is A test' where id=1;
check table tester;
__END__
Gives us:
Table Op Msg_typeMsg_text
freeusall.testercheck warning Table is marked as crashed
freeusall.testercheck error Checksum for key: 2 doesn't
match checksum for records
freeusall.testercheck error Corrupt
We're running MySQL 4.0 alpha on Linux kernel 2.4.2 intel. Using MyISAM
tables.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Locked Processes Taking MySQL down....
Hello,
I am seeing rather frequently our mySQL DB Freezes up. It runs the back end
of a web site that gets decent traffic. When I check the process list for
the DB, there is a bunch of processes all locking that table sitting there.
I normally disable connections from the Cold Fusion server, meaning I do not
let the Cold Fusion server leave an open connection to the database. When
it is done with it's transaction, it disconnects the connection, so usually
when I check the process list, there is no more than 5 processes at a time.
the reason I turned of leaving open connections is because I was seeing a
problem where idle connections would be left in a sleep state and never
disconnected... so I would end up using up all of the connections. once
this would happen, I would have to restart mySQL to purge off the zombied
threads. I have a hunch the connections should be allowed to stay, and the
CF server should be smart enough to continually use the same connection, but
since that did not seem to be the case, I disabled it.
Anyhow, does anyone know why processes would stack up, all with a state of
locked? Is there any countermeasure I can take, or something I am doing
wrong?
Dan Uyemura
IT
GSociety, Inc.
7060 Hollywood Blvd. #800
Hollywood, CA 90028
(v) (323) 337-0141
GSociety family of companies:
www.gaywired.com
www.lesbianation.com
www.qtmagazine.com
www.dance1.net
www.ghighway.com
-Original Message-
From: Mark Maunder [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 2:01 PM
To: [EMAIL PROTECTED]
Subject: MYSQL 4.0 bug with fulltext (case change) updates
Hi,
I think this is a bug. The script to recreate the problem is included
below. This problem appears consistently as long as there's a fulltext
index and a regular index on the same field and you do an update to
change the case of a single char. It doesn't matter if the fulltext
index includes other fields. If the regular index is removed, then after
the insert (where the case of one char in a field is changed) the table
is briefly marked as crashed, and then seems to automagically fix itself
(which is worrying).
The bug also occurs when you change the case of a single char in a
varchar field and add a word to the sentence e.g.:
'experience with c required' changes to 'experience with C is required'
also causes the problem.
Thanks as always,
~mark
ps: If anyone knows of a workaround please let me know. thanks.
__BEGIN__
#This works fine on mysql 3 but crashes the table on 4 alpha
CREATE TABLE tester (
id int(11) auto_increment,
title varchar(100) default '',
PRIMARY KEY (id),
#If you comment out the following index, then the table is briefly
marked as crashed
# but appears to recover.
KEY ind5 (title),
#The fulltext index appears to be the root of this prob.
FULLTEXT KEY FT1 (title)
) TYPE=MyISAM;
insert into tester (title) values ('this is a test');
update tester set title='this is A test' where id=1;
check table tester;
__END__
Gives us:
Table Op Msg_typeMsg_text
freeusall.testercheck warning Table is marked as crashed
freeusall.testercheck error Checksum for key: 2 doesn't
match checksum for records
freeusall.testercheck error Corrupt
We're running MySQL 4.0 alpha on Linux kernel 2.4.2 intel. Using MyISAM
tables.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: Where are the best Access -- Mysql FAQs
Hi Howard, I would start with the bottom of every message. Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) I like this: http://www.bitbybit.dk/mysqlfaq/faq.html http://www.dwam.net/MySQL/ and this: http://netgraft.com/~mbac/research/mysqlmyths.html Have a great weekend, Ken - Original Message - From: howard gramer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 19, 2001 4:29 PM Subject: Where are the best Access -- Mysql FAQs Brand new Newbie here! My first post of any meaning! Considering the futile exercise of building an Access frontend to MySql. Where are the best FAQs, Books, Websites? Thanks howard gramer Home of NYPD and FDNY Environment..: Telecommunications at the circuit level. Email: [EMAIL PROTECTED]Flower: 124096153 MSN Messenger: howard [EMAIL PROTECTED] All communications from me are grounded in opinion. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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 4.0 bug with fulltext (case change) updates
Mark Maunder wrote: Hi, I think this is a bug. The script to recreate the problem is included below. This problem appears consistently as long as there's a fulltext index and a regular index on the same field and you do an update to change the case of a single char. It doesn't matter if the fulltext index includes other fields. If the regular index is removed, then after the insert (where the case of one char in a field is changed) the table is briefly marked as crashed, and then seems to automagically fix itself Just an update to the original post: The table doesn't crash, but the update does not succeed if the regular index is removed - so this isn't a workaround. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Server Problems
When I try to connect a client to MySQL server on RH Linux 7.0 box I get this message window Host 'name.name.net' is not allowed to connect to this MySQL server I am new to the software and I could really use the help. The MySQL client is running on a Windows 2000 box. I have put the my.cnf file in /etc and /var/lib/mysql the default location for the data and tried different configurations and some will let me connect but they will not let me look at tables or create new ones. Thanks Kyle - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem doing bulk and regular inserts
I have some nice database for a biotech lab running on the windows
version of mysql 3.23 on a W2K server. For some reason when my ID or
Auto-increment field get to 127 I can't insert any additional records. I
get the following error:
Error
SQL-query : [Edit]
insert into curvesdb (Genus, Species, Strain, SampleID, BNID, Primer,
LaneNo, CurveData, DCD_Name) VALUES('Xanthomonas', ' bromi', '', 'LMG
8269', '01-052-0094', 'Uprime-B1', '440-442-46',
'0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,2,2,1,1,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,2,2,2,2,2,2,2,1,1,1,1,1,1,1,1,1
,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,3,5,7,9,10,10,9,8,6,5,3,1,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,1,2,4,5,6,6,6,6,5,5,5,5,5,5,5,5,5,4,3,2,1,1,0,0
,0,0,0,0,0,0,1,2,2,3,4,5,5,4,2,1,1,1,4,7,13,20,25,31,34,34,33,27,22,15,9
,5,1,0,0,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,2,4,6,8,9,9,9,7,5,3,1,0,0,0,0,1,1
,1,1,0,0,0,0,0,1,5,12,19,28,36,41,46,44,41,34,25,17,9,3,1,0,0,0,0,0,0,1,
2,4,6,10,13,17,20,23,25,25,23,22,18,14,10,6,4,2,2,3,4,5,6,6,6,4,3,2,1,1,
1,2,2,3,3,3,1,0,0,0,0,1,3,10,18,27,37,43,47,49,45,41,36,31,34,38,47,59,7
1,83,93,92,91,80,66,52,36,21,12,4,3,3,4,6,7,7,6,4,3,1,0,0,0,0,0,0,0,0,0,
0,1,1,2,3,4,5,6,9,13,19,28,36,46,56,62,68,68,64,58,47,36,24,12,6,1,0,0,0
,2,4,4,4,4,4,3,2,0,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,2,4,8,12,15,19,22,22,
22,20,17,14,11,8,6,3,3,3,2,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,3,5,7,9,10,10
,10,9,8,7,7,6,6,6,6,7,8,10,14,18,29,43,59,81,102,123,144,154,162,159,148
,134,110,87,64,41,26,14,5,2,0,1,3,4,6,6,6,6,5,5,5,5,5,5,5,5,5,4,3,2,1,0,
0,0,0,0,3,8,15,26,37,47,58,64,67,68,61,53,42,30,20,11,5,3,1,1,2,3,3,3,2,
1,1,0,0,0,0,0,0,0,0,1,4,7,9,12,12,13,13,13,13,13,13,15,17,21,25,27,29,29
,26,24,19,14,9,5,1,0,0,0,0,0,2,3,4,6,6,7,7,7,6,5,3,2,1,0,0,0,0,0,0,0,1,2
,3,3,2,2,2,1,1,1,1,1,3,4,8,12,16,20,24,25,26,25,22,19,14,10,6,2,1,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,2,2,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,2,3,4,4,5,6,7,7,6,5,4,3,2,1,0,0,0,0,
0,0,0,0,0,1,1,1,1,1,2,2,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,1,2,2,3,3,3,4,4,6,
9,14,19,26,35,44,53,63,69,74,75,72,68,59,50,39,29,21,15,11,9,8,8,8,9,9,8
,7,6,5,3,2,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,1
,1,1,1,1,0,', 'Xanthomonas-B1')
MySQL said:
Duplicate entry '127' for key 1
I can not insert any more data to this table. This is an important
issue, please help me to resolve this.
--
Robert Trembath
Internet Technology Manager
e|[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: indexing question
See: http://www.mysql.com/doc/C/R/CREATE_INDEX.html http://www.mysql.com/doc/M/y/MySQL_indexes.html Steve Meyers -Original Message- From: Michael [mailto:[EMAIL PROTECTED]] Sent: Friday, October 19, 2001 2:07 PM To: [EMAIL PROTECTED] Subject: indexing question Can anyone give me some tips on how indexes work? I noticed that UNIQUE() seems to create a lock on all given fields per call like UNIQUE (md5, mime) so that no row can have the same combination of md5 and mime type which is good but assuming I wanted to have each unique on it's own I'd need UNIQUE (md5), UNIQUE (path) right? Are INDEX's the same as KEY's? When I INDEX() something it seems to dump as a KEY(). Does it matter if you INDEX ( path, md5, mime ) or INDEX(path), INDEX(md5), INDEX(mime)? They seem to look different in the db depending how I do it but both seem to run at about the same speed even over a large db. Either is a huge speed increase over the original that had only primary keys it seems. Right now in a db with several tables.. some with hundreds of thousands of rows.. the average query time seems to be about half a second to a second on a P133. Is that good, bad, or normal? *^*^*^* Michael McGlothlin [EMAIL PROTECTED] http://mlug.missouri.edu/~mogmios/projects/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem doing bulk and regular inserts
Robert Trembath wrote: I have some nice database for a biotech lab running on the windows version of mysql 3.23 on a W2K server. For some reason when my ID or Auto-increment field get to 127 I can't insert any additional records. I get the following error: Error SQL-query : [Edit] snipped Duplicate entry '127' for key 1 I can not insert any more data to this table. This is an important issue, please help me to resolve this. Change the field type for key 1 to an int (not a tinyint). Van -- = Linux rocks!!! http://www.dedserius.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 doing bulk and regular inserts
In the last episode (Oct 19), Robert Trembath said: I have some nice database for a biotech lab running on the windows version of mysql 3.23 on a W2K server. For some reason when my ID or Auto-increment field get to 127 I can't insert any additional records. I get the following error: Change your autoincrement field from tinyint to something bigger. Tinyint's range is, well, tiny: -127 to 127. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem doing bulk and regular inserts
Tried turning force on so that it'll ignore errors? That way only
duplicate rows are ignored?
*^*^*^*
Michael McGlothlin [EMAIL PROTECTED]
http://mlug.missouri.edu/~mogmios/projects/
On Fri, 19 Oct 2001, Robert Trembath wrote:
I have some nice database for a biotech lab running on the windows
version of mysql 3.23 on a W2K server. For some reason when my ID or
Auto-increment field get to 127 I can't insert any additional records. I
get the following error:
Error
SQL-query : [Edit]
insert into curvesdb (Genus, Species, Strain, SampleID, BNID, Primer,
LaneNo, CurveData, DCD_Name) VALUES('Xanthomonas', ' bromi', '', 'LMG
8269', '01-052-0094', 'Uprime-B1', '440-442-46',
'0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,2,2,1,1,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,2,2,2,2,2,2,2,1,1,1,1,1,1,1,1,1
,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,3,5,7,9,10,10,9,8,6,5,3,1,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,1,2,4,5,6,6,6,6,5,5,5,5,5,5,5,5,5,4,3,2,1,1,0,0
,0,0,0,0,0,0,1,2,2,3,4,5,5,4,2,1,1,1,4,7,13,20,25,31,34,34,33,27,22,15,9
,5,1,0,0,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,2,4,6,8,9,9,9,7,5,3,1,0,0,0,0,1,1
,1,1,0,0,0,0,0,1,5,12,19,28,36,41,46,44,41,34,25,17,9,3,1,0,0,0,0,0,0,1,
2,4,6,10,13,17,20,23,25,25,23,22,18,14,10,6,4,2,2,3,4,5,6,6,6,4,3,2,1,1,
1,2,2,3,3,3,1,0,0,0,0,1,3,10,18,27,37,43,47,49,45,41,36,31,34,38,47,59,7
1,83,93,92,91,80,66,52,36,21,12,4,3,3,4,6,7,7,6,4,3,1,0,0,0,0,0,0,0,0,0,
0,1,1,2,3,4,5,6,9,13,19,28,36,46,56,62,68,68,64,58,47,36,24,12,6,1,0,0,0
,2,4,4,4,4,4,3,2,0,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,2,4,8,12,15,19,22,22,
22,20,17,14,11,8,6,3,3,3,2,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,3,5,7,9,10,10
,10,9,8,7,7,6,6,6,6,7,8,10,14,18,29,43,59,81,102,123,144,154,162,159,148
,134,110,87,64,41,26,14,5,2,0,1,3,4,6,6,6,6,5,5,5,5,5,5,5,5,5,4,3,2,1,0,
0,0,0,0,3,8,15,26,37,47,58,64,67,68,61,53,42,30,20,11,5,3,1,1,2,3,3,3,2,
1,1,0,0,0,0,0,0,0,0,1,4,7,9,12,12,13,13,13,13,13,13,15,17,21,25,27,29,29
,26,24,19,14,9,5,1,0,0,0,0,0,2,3,4,6,6,7,7,7,6,5,3,2,1,0,0,0,0,0,0,0,1,2
,3,3,2,2,2,1,1,1,1,1,3,4,8,12,16,20,24,25,26,25,22,19,14,10,6,2,1,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,2,2,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,2,3,4,4,5,6,7,7,6,5,4,3,2,1,0,0,0,0,
0,0,0,0,0,1,1,1,1,1,2,2,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,1,2,2,3,3,3,4,4,6,
9,14,19,26,35,44,53,63,69,74,75,72,68,59,50,39,29,21,15,11,9,8,8,8,9,9,8
,7,6,5,3,2,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,1
,1,1,1,1,0,', 'Xanthomonas-B1')
MySQL said:
Duplicate entry '127' for key 1
I can not insert any more data to this table. This is an important
issue, please help me to resolve this.
--
Robert Trembath
Internet Technology Manager
e|[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Locked Processes Taking MySQL down....
All of the locked queries are happening because another query has locked them out.
That is, until a certain query finishes, none of the others can execute. See the
manual for more info on table locking. You can generally fix this simply by fixing
your bad queries (adding proper indexes, etc), but if this will not work, switching to
InnoDB may be the most beneficial for you.
http://www.mysql.com/doc/T/a/Table_locking.html
Steve Meyers
-Original Message-
From: Dan Uyemura [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 3:31 PM
To: mySQL List
Subject: Locked Processes Taking MySQL down
Hello,
I am seeing rather frequently our mySQL DB Freezes up. It runs
the back end
of a web site that gets decent traffic. When I check the process list for
the DB, there is a bunch of processes all locking that table
sitting there.
I normally disable connections from the Cold Fusion server,
meaning I do not
let the Cold Fusion server leave an open connection to the database. When
it is done with it's transaction, it disconnects the connection,
so usually
when I check the process list, there is no more than 5 processes
at a time.
the reason I turned of leaving open connections is because I was seeing a
problem where idle connections would be left in a sleep state and never
disconnected... so I would end up using up all of the connections. once
this would happen, I would have to restart mySQL to purge off the
zombied
threads. I have a hunch the connections should be allowed to
stay, and the
CF server should be smart enough to continually use the same
connection, but
since that did not seem to be the case, I disabled it.
Anyhow, does anyone know why processes would stack up, all with a state of
locked? Is there any countermeasure I can take, or something I am doing
wrong?
Dan Uyemura
IT
GSociety, Inc.
7060 Hollywood Blvd. #800
Hollywood, CA 90028
(v) (323) 337-0141
GSociety family of companies:
www.gaywired.com
www.lesbianation.com
www.qtmagazine.com
www.dance1.net
www.ghighway.com
-Original Message-
From: Mark Maunder [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 2:01 PM
To: [EMAIL PROTECTED]
Subject: MYSQL 4.0 bug with fulltext (case change) updates
Hi,
I think this is a bug. The script to recreate the problem is included
below. This problem appears consistently as long as there's a fulltext
index and a regular index on the same field and you do an update to
change the case of a single char. It doesn't matter if the fulltext
index includes other fields. If the regular index is removed, then after
the insert (where the case of one char in a field is changed) the table
is briefly marked as crashed, and then seems to automagically fix itself
(which is worrying).
The bug also occurs when you change the case of a single char in a
varchar field and add a word to the sentence e.g.:
'experience with c required' changes to 'experience with C is required'
also causes the problem.
Thanks as always,
~mark
ps: If anyone knows of a workaround please let me know. thanks.
__BEGIN__
#This works fine on mysql 3 but crashes the table on 4 alpha
CREATE TABLE tester (
id int(11) auto_increment,
title varchar(100) default '',
PRIMARY KEY (id),
#If you comment out the following index, then the table is briefly
marked as crashed
# but appears to recover.
KEY ind5 (title),
#The fulltext index appears to be the root of this prob.
FULLTEXT KEY FT1 (title)
) TYPE=MyISAM;
insert into tester (title) values ('this is a test');
update tester set title='this is A test' where id=1;
check table tester;
__END__
Gives us:
Table Op Msg_typeMsg_text
freeusall.testercheck warning Table is marked as crashed
freeusall.testercheck error Checksum for key: 2 doesn't
match checksum for records
freeusall.testercheck error Corrupt
We're running MySQL 4.0 alpha on Linux kernel 2.4.2 intel. Using MyISAM
tables.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the
field value NULL compares equal to NULL
David Hi!
David
David When you have a indexed field that allows NULL (as per mysql = 3.23.2)
David and there is more than one row that has null in that column,
David then an equality comparison between that field and NULL returns true
David if the index is used.
David
David The problem does not occur if the field is not indexed.
David Comparisons with IS [NOT] NULL continue to function correctly.
David
David How-To-Repeat:
David
David Simplest reproducing SQL:
David
David DROP TABLE IF EXISTS test_table;
David
David CREATE TABLE test_table (
David indexed_field int default NULL,
David KEY indexed_field (indexed_field)
David ) TYPE=MyISAM;
David
David INSERT INTO test_table VALUES (NULL);
David INSERT INTO test_table VALUES (NULL);
David
David select * from test_table where indexed_field=NULL;
David
David +---+
David | indexed_field |
David +---+
David | NULL |
David | NULL |
David +---+
David 2 rows in set (0.00 sec)
Here is a patch that fixes this problem. This will be in the next MySQL
release (both 3.23.44 and 4.0.1)
= sql/sql_select.cc 1.105 vs edited =
*** /tmp/sql_select.cc-1.105-21548 Thu Sep 27 22:02:37 2001
--- edited/sql/sql_select.ccFri Oct 19 21:44:30 2001
***
*** 4963,4977
static bool test_if_ref(Item_field *left_item,Item *right_item)
{
Field *field=left_item-field;
! if (!field-table-const_table) // No need to change const test
{
Item *ref_item=part_of_refkey(field-table,field);
if (ref_item ref_item-eq(right_item))
{
if (right_item-type() == Item::FIELD_ITEM)
! return (field-eq_def(((Item_field *) right_item)-field)
! !field-table-maybe_null);
! if (right_item-const_item())
{
// We can remove binary fields and numerical fields except float,
// as float comparison isn't 100 % secure
--- 4963,4978
static bool test_if_ref(Item_field *left_item,Item *right_item)
{
Field *field=left_item-field;
! // No need to change const test. We also have to keep tests on LEFT JOIN
! if (!field-table-const_table !field-table-maybe_null)
{
Item *ref_item=part_of_refkey(field-table,field);
if (ref_item ref_item-eq(right_item))
{
if (right_item-type() == Item::FIELD_ITEM)
! return (field-eq_def(((Item_field *) right_item)-field));
! if (right_item-const_item()
! (right_item-val_int() || !right_item-null_value))
{
// We can remove binary fields and numerical fields except float,
// as float comparison isn't 100 % secure
Both problems was related to the following optimization:
If you use the following construct in MySQL:
WHERE index_column=constant
and MySQL internally uses 'constant' to look up the field through an
index (a 'ref' key), then MySQL will remove the
'index_column=constant' expression from the WHERE clause, because the
test will implicitly done by the index lookup function.
This optimization doesn't however work when you are using a LEFT JOIN
or when comparing with = NULL.
Regards,
Monty
--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Frequently corrupt tables
Bill Adams wrote: Spoiler: You may be right about the bad libs... [snip] *** OMG *** But haha I cannot believe this, I was just looking at the libraries linked by mysqld with ldd and it is using the informix libpthread.so. Hmm, crap. *me slaps head* Small Update: o If there is no call to 'flush tables', even a small data load will cause myisamcheck to report warning: 1 clients is using or hasn't closed the table properly when I know there is no client accessing it. In this case myisamcheck does fix the problem. o If I call 'flush tables' even at the program exit, I do not get the warning. o Using the statically linked 4.0 binary from mysql.com had no effect on the results. o Upgrading DBI to the latest version had no effect on the results. o Upgrading to the latest msql-mysql-moudles did not effect the results. I was playing around with the 'flush tables' and managed to quickly corrupt a table without any flush call. I saved a copy of the table if the mysql folks should want to take a look for some unknown reason. [bill@host ~/dev]$ /usr/local/mysql-4.0/bin/myisamchk -e ../bad-tables/pcm_test_site_200105.MYI Checking MyISAM file: ../bad-tables/pcm_test_site_200105.MYI Data records: 58923 Deleted blocks: 0 /usr/local/mysql-4.0/bin/myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check records and index references /usr/local/mysql-4.0/bin/myisamchk: error: Record-count is not ok; is 58328 Should be: 58923 /usr/local/mysql-4.0/bin/myisamchk: warning: Found595 deleted blocks Should be: 0 MyISAM-table '../bad-tables/pcm_test_site_200105.MYI' is corrupted Fix it using switch -r or -o But running myisamchk without the -e just gives the 'clients using' warning. Also, '-r' will repair the table in this case and adding the flush back in seems to prevent the error from happening. Well, it is almost beer o'clock. I will try this on other machines on Monday. --Bill - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: Can Mysql search full text fast through 3.3 million text documents?
- Original Message - From: Barbara Ferrell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 19, 2001 6:51 PM Subject: Fw: Can Mysql search full text fast through 3.3 million text documents? We have 3.3 million text documents ( U.S. Patents) that we can put into any format needed to import into any database needed. Would Mysql be able to handle fast FULLTEXT searching? Each document has several fields that would need to be searched, for example: we would want to search all 3.3 million documents for the database and relation, and sometimes limit the search to specific fields per document. .the database is about 200 gigs. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
error when installing mysql, plz help
I typed './mysqladmin -u root -p password my new password' and this came up, ideas on what I could do to solve this problem that I'm having? By the way, I'm using the MySQL 3.23 ./mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! In need of desperate help, Dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: indexing question
Thanks. I've read the manual. I guess I was looking for a more direct explanation to make sure I had it clear and to learn any tips that might be useful that wouldn't be in the manual. As my database will be quite large I'm worried about effective optimizations. http://www.mysql.com/doc/C/R/CREATE_INDEX.html http://www.mysql.com/doc/M/y/MySQL_indexes.html Can anyone give me some tips on how indexes work? I noticed that UNIQUE() seems to create a lock on all given fields per call like UNIQUE (md5, mime) so that no row can have the same combination of md5 and mime type which is good but assuming I wanted to have each unique on it's own I'd need UNIQUE (md5), UNIQUE (path) right? Are INDEX's the same as KEY's? When I INDEX() something it seems to dump as a KEY(). Does it matter if you INDEX ( path, md5, mime ) or INDEX(path), INDEX(md5), INDEX(mime)? They seem to look different in the db depending how I do it but both seem to run at about the same speed even over a large db. Either is a huge speed increase over the original that had only primary keys it seems. Right now in a db with several tables.. some with hundreds of thousands of rows.. the average query time seems to be about half a second to a second on a P133. Is that good, bad, or normal? *^*^*^* Michael McGlothlin [EMAIL PROTECTED] http://mlug.missouri.edu/~mogmios/projects/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
error when installing mysql, plz help
I typed './mysqladmin -u root -p password my new password' and this came up, ideas on what I could do to solve this problem that I'm having? By the way, I'm using the MySQL 3.23 ./mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! In need of desperate help, Dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: error when installing mysql, plz help
David Loszewski writes: I typed './mysqladmin -u root -p password my new password' and this came up, ideas on what I could do to solve this problem that I'm having? Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! Start mysqld before you try to connect to it. If it doesn't start when you try to start it, read the error log. If all else fails, read the FAQ. http://www.bitbybit.dk/mysqlfaq/ Also, please not that it may tike more than a few minutes before a mail to this mailing list is sent back out to you. Don't repost unless you're fairly sure that your mail didn't get through, i.e., wait for other mails to appear, and check if they were sent after your mail. (This list has a tendency to send mails in burst. I'm not sure whether this is because of the server or the users' habits.) //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem doing bulk and regular inserts
You haven't given your table definition, but I'm guessing that your primary key is a
TINYINT, which only supports -128 to +127. Figure out how big you need that key to
be, then change the column as appropriate to a SMALLINT, MEDIUMINT, INT, or BIGINT.
Steve Meyers
-Original Message-
From: Robert Trembath [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 4:34 PM
To: [EMAIL PROTECTED]
Subject: Problem doing bulk and regular inserts
I have some nice database for a biotech lab running on the windows
version of mysql 3.23 on a W2K server. For some reason when my ID or
Auto-increment field get to 127 I can't insert any additional records. I
get the following error:
Error
SQL-query : [Edit]
insert into curvesdb (Genus, Species, Strain, SampleID, BNID, Primer,
LaneNo, CurveData, DCD_Name) VALUES('Xanthomonas', ' bromi', '', 'LMG
8269', '01-052-0094', 'Uprime-B1', '440-442-46',
'0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,2,2,1,1,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,2,2,2,2,2,2,2,1,1,1,1,1,1,1,1,1
,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,3,5,7,9,10,10,9,8,6,5,3,1,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,1,2,4,5,6,6,6,6,5,5,5,5,5,5,5,5,5,4,3,2,1,1,0,0
,0,0,0,0,0,0,1,2,2,3,4,5,5,4,2,1,1,1,4,7,13,20,25,31,34,34,33,27,22,15,9
,5,1,0,0,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,2,4,6,8,9,9,9,7,5,3,1,0,0,0,0,1,1
,1,1,0,0,0,0,0,1,5,12,19,28,36,41,46,44,41,34,25,17,9,3,1,0,0,0,0,0,0,1,
2,4,6,10,13,17,20,23,25,25,23,22,18,14,10,6,4,2,2,3,4,5,6,6,6,4,3,2,1,1,
1,2,2,3,3,3,1,0,0,0,0,1,3,10,18,27,37,43,47,49,45,41,36,31,34,38,47,59,7
1,83,93,92,91,80,66,52,36,21,12,4,3,3,4,6,7,7,6,4,3,1,0,0,0,0,0,0,0,0,0,
0,1,1,2,3,4,5,6,9,13,19,28,36,46,56,62,68,68,64,58,47,36,24,12,6,1,0,0,0
,2,4,4,4,4,4,3,2,0,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,2,4,8,12,15,19,22,22,
22,20,17,14,11,8,6,3,3,3,2,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,3,5,7,9,10,10
,10,9,8,7,7,6,6,6,6,7,8,10,14,18,29,43,59,81,102,123,144,154,162,159,148
,134,110,87,64,41,26,14,5,2,0,1,3,4,6,6,6,6,5,5,5,5,5,5,5,5,5,4,3,2,1,0,
0,0,0,0,3,8,15,26,37,47,58,64,67,68,61,53,42,30,20,11,5,3,1,1,2,3,3,3,2,
1,1,0,0,0,0,0,0,0,0,1,4,7,9,12,12,13,13,13,13,13,13,15,17,21,25,27,29,29
,26,24,19,14,9,5,1,0,0,0,0,0,2,3,4,6,6,7,7,7,6,5,3,2,1,0,0,0,0,0,0,0,1,2
,3,3,2,2,2,1,1,1,1,1,3,4,8,12,16,20,24,25,26,25,22,19,14,10,6,2,1,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,2,2,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,2,3,4,4,5,6,7,7,6,5,4,3,2,1,0,0,0,0,
0,0,0,0,0,1,1,1,1,1,2,2,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,1,2,2,3,3,3,4,4,6,
9,14,19,26,35,44,53,63,69,74,75,72,68,59,50,39,29,21,15,11,9,8,8,8,9,9,8
,7,6,5,3,2,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,1
,1,1,1,1,0,', 'Xanthomonas-B1')
MySQL said:
Duplicate entry '127' for key 1
I can not insert any more data to this table. This is an important
issue, please help me to resolve this.
--
Robert Trembath
Internet Technology Manager
e|[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: indexing question
Okay, then I'll go through it point by point :) Thanks. I've read the manual. I guess I was looking for a more direct explanation to make sure I had it clear and to learn any tips that might be useful that wouldn't be in the manual. As my database will be quite large I'm worried about effective optimizations. Can anyone give me some tips on how indexes work? I noticed that UNIQUE() seems to create a lock on all given fields per call like UNIQUE (md5, mime) so that no row can have the same combination of md5 and mime type which is good but assuming I wanted to have each unique on it's own I'd need UNIQUE (md5), UNIQUE (path) right? Yes, that's correct Are INDEX's the same as KEY's? When I INDEX() something it seems to dump as a KEY(). Does it matter if you INDEX ( path, md5, mime ) or INDEX(path), INDEX(md5), INDEX(mime)? They seem to look different in the db depending how I do it but both seem to run at about the same speed even over a large db. Either is a huge speed increase over the original that had only primary keys it seems. Yes they're the same. How you index depends on your queries. Generally, just look at what your doing in your where clauses. If you're looking up rows based just on the path, then index path. If you're looking up rows based on path, md5, and mime at the same time, then index across all three. If you're doing both of the above, then index across all three -- any prefix of an index can be used as an index. Right now in a db with several tables.. some with hundreds of thousands of rows.. the average query time seems to be about half a second to a second on a P133. Is that good, bad, or normal? Depends on the query :) On a well-indexed table, a simple select should report no more than a tenth of a second, even on a P133. Of course, that partly depends on how much memory you have... More memory is always a good investment. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
foreign key
hi, which windows mysql version supports foreign key and its complete implementation: referential integrity constraint, avoid delete parent key, etc.? or Which foreign key features that has the best windows mysql version? thanks, sandra. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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: join tables on UPDATE
thank you
Steve Meyers wrote:
That will be in some release of either 4.0 or 4.1, I'm not sure which.
=20
Steve Meyers
-Original Message-
From: JohnHomer [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 2:11 AM
To: [EMAIL PROTECTED]
Subject: join tables on UPDATE
=20
=20
hi list,
=20
can mysql allow table joins when using UPDATE query? like
=20
update tranfer left join transferdetails
on transfer.docno =3D transferdetails.docno
set transferdetails.docno =3D concat('SJ',transferdetails.DocNo)
where
transferdetails.docno not regexp '^SJ' and
lower(xfrom) =3D 'san juan';
=20
=20
i get an error=20
=20
=20
ERROR 1064 at line 1: You have an error in your SQL syntax near=20
'left join trans ferdetails on transfer.docno =3D=20
transferdetails.docno set transfer' at line 1
=20
im using mysql 3.23.41 on win32
=20
=20
TIA
=20
johnhomer
=20
=20
=20
__
www.edsamail.com
=20
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
=20
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail=20
=
[EMAIL PROTECTED]=
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
=20
=20
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
__
www.edsamail.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
XML support
When will mysql support XML with function same as Oracle XSU? Kenneth. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: foreign key
I'm pretty sure that the complete implementation is not done yet, in the sense that there is no RDI enforcement. Good Luck, Dennis ** Beridney Computer Services [EMAIL PROTECTED] http://www.beridney.com - Original Message - From: Sandra Rovena Frigeri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 19, 2001 7:24 PM Subject: foreign key hi, which windows mysql version supports foreign key and its complete implementation: referential integrity constraint, avoid delete parent key, etc.? or Which foreign key features that has the best windows mysql version? thanks, sandra. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: indexing question
Okay, then I'll go through it point by point :) Thanks. Helps a lot. Yes they're the same. How you index depends on your queries. Generally, just look at what your doing in your where clauses. If you're looking up rows based just on the path, then index path. If you're looking up rows based on path, md5, and mime at the same time, then index across all three. If you're doing both of the above, then index across all three -- any prefix of an index can be used as an index. So there is no magic bullet that indexes everything so it works well with any given query? Does it help to index each field by itself for general queries and then I guess you index combinations of fields that will be used together in a WHERE clause? Does ORDER BY use indexes too? Would this need an index by md5 and mime or md5, mime, and path? SELECT * from files WHERE md5 = 'blah' AND mime = 'blah' ORDER BY path; Depends on the query :) On a well-indexed table, a simple select should report no more than a tenth of a second, even on a P133. Of course, that partly depends on how much memory you have... More memory is always a good investment. Guess I'll hafta work on it. My memory is limited to 32M on this development machine but I figure the more the development machine sucks then the faster the code should run on the end server if the code runs okay on the dev box. :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: indexing question
So there is no magic bullet that indexes everything so it works well with any given query? Does it help to index each field by itself for general queries and then I guess you index combinations of fields that will be used together in a WHERE clause? Nope, no magic bullet... Indexes speed up selects and sometimes updates, but slow down inserts, deletes, and sometimes updates (for updates it depends on the query and the data, since it can use an index but may also have to update an index). So don't use them where they're not needed. Most data has only a few ways that it is viewed. I probably wouldn't add an index that would only be used once a month :) Does ORDER BY use indexes too? Would this need an index by md5 and mime or md5, mime, and path? SELECT * from files WHERE md5 = 'blah' AND mime = 'blah' ORDER BY path; In that case, I think the index across all three, in that order, would help (from what I know of the how MySQL is supposed to work). Guess I'll hafta work on it. My memory is limited to 32M on this development machine but I figure the more the development machine sucks then the faster the code should run on the end server if the code runs okay on the dev box. :) Sometimes the biggest difference between dev and live isn't the box it's running on, it's the amount and types of concurrent queries. That can make a huge difference, especially on MyISAM tables. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: foreign key
InnoDB tables support foreign keys with full referential integrity constraints. They do not yet support cascading deletes and updates. You'll want to use the MySQL-Max version of MySQL to get support for InnoDB tables. Steve Meyers -Original Message- From: Sandra Rovena Frigeri [mailto:[EMAIL PROTECTED]] Sent: Friday, October 19, 2001 8:25 PM To: [EMAIL PROTECTED] Subject: foreign key hi, which windows mysql version supports foreign key and its complete implementation: referential integrity constraint, avoid delete parent key, etc.? or Which foreign key features that has the best windows mysql version? thanks, sandra. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: XML support
Since this is an open source product, it could happen a lot sooner if you wrote it :) I don't know of any plans to include that, at least in the near future. Steve Meyers -Original Message- From: can [mailto:[EMAIL PROTECTED]] Sent: Friday, October 19, 2001 8:14 PM To: [EMAIL PROTECTED] Subject: XML support When will mysql support XML with function same as Oracle XSU? Kenneth. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: how to get the correct result -- Thrid Time --
On Fri, 19 Oct 2001, Steve Meyers wrote: Well, you still have white space in there. Try running the following until it's fixed: UPDATE hotel SET nome_hotel=replace(nome_hotel, ' ', ' ') That will replace any double spaces to one space. If there's still a problem, replace all tabs (\t) and carriage returns (\r) with a space, and repeat the above. And from now on, make sure the data gets put in right :) Well that would only get the first two spaces. I do not know how many spaces are there Adrian Steve Meyers -Original Message- From: Adrian D'Costa [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 18, 2001 11:22 PM To: Steve Meyers Cc: DL Neil; Mysql Mailing List Subject: RE: how to get the correct result -- Thrid Time -- On Thu, 18 Oct 2001, Steve Meyers wrote: Trim won't work because the newline is in the middle of the string. Try using the REPLACE function, ie UPDATE hotel SET nome_hotel=replace(nome_hotel, '\n', '') Ok it replaced it. For the second question, it actually doesn't matter if it's CAPS or not. The group by will be done in a case insensitive manner. However, if you really need it lowercase, try this: SELECT LOWER(nome_hotel) FROM hotel GROUP BY 1 The result is mysql SELECT id, LOWER(nome_hotel) FROM hotel GROUP BY 2 limit 10; +--+-+ | id | LOWER(nome_hotel) | +--+-+ | 3825 | | | 3391 | abi d'oru | | 5208 | abou nawas djerba | | 3063 | abou nawas djerba | | 1252 | adams beach | | 9757 | aegean village | | 7973 | aegean | | 8917 | aegean village | | 8122 | aegeon | | 8284 | aeneas | +--+-+ Atain 5208 and 3063 are the same so is 9757 and 8917. I just want only one of them to appear. Thanks Adrian Steve Meyers -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 18, 2001 4:46 AM To: Adrian D'Costa; Mysql Mailing List Subject: Re: how to get the correct result -- Thrid Time -- You didn't appear to answer last time (grump) - reply repeated below. =dn - Original Message - From: Adrian D'Costa [EMAIL PROTECTED] To: Mysql Mailing List [EMAIL PROTECTED] Sent: 18 October 2001 05:34 Subject: how to get the correct result -- Thrid Time -- Hi, I have some records that I need to group by a field. This is easy using group by in the sql statement. What I happening is that I get some data in html format that I have written a script that will extract the data I require an dump it into a table. Everything working fine. The problem is that when the data in entered into the table some fields enter with the new line (\n). So when I use the group by command below is the result. mysql select nome_hotel from hotel group by nome_hotel limit 10; | Abou Nawas Djerba | | Abou Nawas Djerba| | Adams Beach | | Aegean Village | | Aegean | | Aegean Village | This what I don't want since Abou Nawas Djerba and Abou Nawas Djerba are the same. How do I get rid of the space. I tried trim, rtrim nothing works. Second, using the same data I get some of the hotel names in CAPS and I need to convert it to lower and then group by. How do I get these two done? Thanks Adrian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php I have some records that I need to group by a field. This is easy using ... that when the data in entered into the table some fields enter with the new line (\n). So when I use the group by command below is the result. ... Second, using the same data I get some of the hotel names in CAPS and I need to convert it to lower and then group by. How do I get these two done? Adrian, Can you do these (both) at the time the data is first entered into the db? In other words adjust your business rules at the front end, instead of attempting something that's very difficult at the back? =dn
RE: how to get the correct result -- Thrid Time --
On Thu, 18 Oct 2001, Steve Meyers wrote: Trim won't work because the newline is in the middle of the string. Try using the REPLACE function, ie UPDATE hotel SET nome_hotel=replace(nome_hotel, '\n', '') Ok it replaced it. For the second question, it actually doesn't matter if it's CAPS or not. The group by will be done in a case insensitive manner. However, if you really need it lowercase, try this: SELECT LOWER(nome_hotel) FROM hotel GROUP BY 1 The result is mysql SELECT id, LOWER(nome_hotel) FROM hotel GROUP BY 2 limit 10; +--+-+ | id | LOWER(nome_hotel) | +--+-+ | 3825 | | | 3391 | abi d'oru | | 5208 | abou nawas djerba | | 3063 | abou nawas djerba | | 1252 | adams beach | | 9757 | aegean village | | 7973 | aegean | | 8917 | aegean village | | 8122 | aegeon | | 8284 | aeneas | +--+-+ Atain 5208 and 3063 are the same so is 9757 and 8917. I just want only one of them to appear. Thanks Adrian Steve Meyers -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 18, 2001 4:46 AM To: Adrian D'Costa; Mysql Mailing List Subject: Re: how to get the correct result -- Thrid Time -- You didn't appear to answer last time (grump) - reply repeated below. =dn - Original Message - From: Adrian D'Costa [EMAIL PROTECTED] To: Mysql Mailing List [EMAIL PROTECTED] Sent: 18 October 2001 05:34 Subject: how to get the correct result -- Thrid Time -- Hi, I have some records that I need to group by a field. This is easy using group by in the sql statement. What I happening is that I get some data in html format that I have written a script that will extract the data I require an dump it into a table. Everything working fine. The problem is that when the data in entered into the table some fields enter with the new line (\n). So when I use the group by command below is the result. mysql select nome_hotel from hotel group by nome_hotel limit 10; | Abou Nawas Djerba | | Abou Nawas Djerba| | Adams Beach | | Aegean Village | | Aegean | | Aegean Village | This what I don't want since Abou Nawas Djerba and Abou Nawas Djerba are the same. How do I get rid of the space. I tried trim, rtrim nothing works. Second, using the same data I get some of the hotel names in CAPS and I need to convert it to lower and then group by. How do I get these two done? Thanks Adrian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php I have some records that I need to group by a field. This is easy using ... that when the data in entered into the table some fields enter with the new line (\n). So when I use the group by command below is the result. ... Second, using the same data I get some of the hotel names in CAPS and I need to convert it to lower and then group by. How do I get these two done? Adrian, Can you do these (both) at the time the data is first entered into the db? In other words adjust your business rules at the front end, instead of attempting something that's very difficult at the back? =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try:
finding the range of results?
Short version: How do you find what position a value is in a larger query's result? Long version: I have a thumbnail image viewing page that pages through search results using LIMIT page * page_size, page_size and that table does keep id's from all results but they are not always 1, 2, 3, 4, 5... often they are 1, 3, 6, 7, 9, etc.. in no special pattern but in order.. there is also an image id per each row that is not in order and again in no special pattern.. in cases where the user has clicked an image it uses the image id to view the image.. then when the user wants to return to browse mode it needs to figure out what page to return to. easy.. except they can also flip backwards and forward in the results in view mode so the page could change. So I need to find the page which I can do if I can find the id and it's position in the queries result. How exactly would I find the position in the results without dumping all results and using an external function. Something like position = variable in array. *^*^*^* Michael McGlothlin [EMAIL PROTECTED] http://mlug.missouri.edu/~mogmios/projects/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Duplicate Inserts
I'm running MySQL on a WIN2k server box. I'm doing inserts using PHP. Here's the code for the insert: $db = mysql_connect($hostname, $uid, $pwd); //$hostname is localhost and $uid/$pwd are userid and password mysql_select_db(heroes,$db); $insertQuery = INSERT INTO UniqueCardList (MCardID,OwnerID) VALUES(1,1); echo $insertQuery . BR\n; $insertResult = mysql_query($insertQuery,$db) or die (Insert query failed); I can go in, delete all records from the table, run the code once, and wind up with two records. Anybody got any ideas? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Java applet connection to MySQL?
Hello, I have in mind a site that would require a Java applet to load information from a MySQL database... The applet would pull fields from a row randomly selected each time is is loaded onto a web page on a client computer. Fields would include some text and one picture. Of course, login info has to remain secret, and no parameters should appear in the html code calling the applet. Is this possible? Are there any resources out there on the subject? The problem is that I don't know anything about java :-( Anyone interested in helping me with this? (for a few bucks). Thanks, sincerely, Gil. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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 get the correct result -- Thrid Time --
That's why I told you to keep on running it until it was all fixed. Every time there will be one less white space, until you're down to just one. Steve Meyers -Original Message- From: Adrian D'Costa [mailto:[EMAIL PROTECTED]] Sent: Friday, October 19, 2001 3:44 AM To: Steve Meyers Cc: Mysql Mailing List Subject: RE: how to get the correct result -- Thrid Time -- On Fri, 19 Oct 2001, Steve Meyers wrote: Well, you still have white space in there. Try running the following until it's fixed: UPDATE hotel SET nome_hotel=replace(nome_hotel, ' ', ' ') That will replace any double spaces to one space. If there's still a problem, replace all tabs (\t) and carriage returns (\r) with a space, and repeat the above. And from now on, make sure the data gets put in right :) Well that would only get the first two spaces. I do not know how many spaces are there Adrian Steve Meyers -Original Message- From: Adrian D'Costa [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 18, 2001 11:22 PM To: Steve Meyers Cc: DL Neil; Mysql Mailing List Subject: RE: how to get the correct result -- Thrid Time -- On Thu, 18 Oct 2001, Steve Meyers wrote: Trim won't work because the newline is in the middle of the string. Try using the REPLACE function, ie UPDATE hotel SET nome_hotel=replace(nome_hotel, '\n', '') Ok it replaced it. For the second question, it actually doesn't matter if it's CAPS or not. The group by will be done in a case insensitive manner. However, if you really need it lowercase, try this: SELECT LOWER(nome_hotel) FROM hotel GROUP BY 1 The result is mysql SELECT id, LOWER(nome_hotel) FROM hotel GROUP BY 2 limit 10; +--+-+ | id | LOWER(nome_hotel) | +--+-+ | 3825 | | | 3391 | abi d'oru | | 5208 | abou nawas djerba | | 3063 | abou nawas djerba | | 1252 | adams beach | | 9757 | aegean village | | 7973 | aegean | | 8917 | aegean village | | 8122 | aegeon | | 8284 | aeneas | +--+-+ Atain 5208 and 3063 are the same so is 9757 and 8917. I just want only one of them to appear. Thanks Adrian Steve Meyers -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 18, 2001 4:46 AM To: Adrian D'Costa; Mysql Mailing List Subject: Re: how to get the correct result -- Thrid Time -- You didn't appear to answer last time (grump) - reply repeated below. =dn - Original Message - From: Adrian D'Costa [EMAIL PROTECTED] To: Mysql Mailing List [EMAIL PROTECTED] Sent: 18 October 2001 05:34 Subject: how to get the correct result -- Thrid Time -- Hi, I have some records that I need to group by a field. This is easy using group by in the sql statement. What I happening is that I get some data in html format that I have written a script that will extract the data I require an dump it into a table. Everything working fine. The problem is that when the data in entered into the table some fields enter with the new line (\n). So when I use the group by command below is the result. mysql select nome_hotel from hotel group by nome_hotel limit 10; | Abou Nawas Djerba | | Abou Nawas Djerba| | Adams Beach | | Aegean Village | | Aegean | | Aegean Village | This what I don't want since Abou Nawas Djerba and Abou Nawas Djerba are the same. How do I get rid of the space. I tried trim, rtrim nothing works. Second, using the same data I get some of the hotel names in CAPS and I need to convert it to lower and then group by. How do I get these two done? Thanks Adrian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php I have some records that I need to group by a field. This is easy using ... that when the data in entered into the table some fields enter with the new
MySQL 4.0 table crash when updating record with fulltext index
Description:
When doing the following update I get a table crash.
How-To-Repeat:
CREATE TABLE tester (
id int(11) auto_increment,
title varchar(100) default '',
PRIMARY KEY (id),
#If you comment out the following index, then the table is briefly marked as crashed
# but appears to recover although the update does not succeed
KEY ind5 (title),
#The fulltext index appears to be the root of this prob.
FULLTEXT KEY FT1 (title)
) TYPE=MyISAM;
insert into tester (title) values ('this is a test');
update tester set title='this is A test' where id=1;
check table tester;
Fix:
No known workaround. Please advise if you are aware of one. Thanks.
Submitter-Id: submitter ID
Originator:Mark Maunder
Organization:
SwiftCamel Software LTD
MySQL support: none
Synopsis: Table crash when doing update of record with fulltext index.
Severity: critical
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-4.0.0-alpha (Source distribution)
Server: /usr/bin/mysqladmin Ver 8.22 Distrib 4.0.0-alpha, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.0-alpha-log
Protocol version10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 8 hours 3 min 52 sec
Threads: 32 Questions: 18198 Slow queries: 295 Opens: 33 Flush tables: 1 Open
tables: 12 Queries per second avg: 0.627
Environment:
System: Linux mark.swiftcamel.com 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 Jul 28 17:24 /lib/libc.so.6 - libc-2.2.2.so
-rwxr-xr-x2 root root 1236396 Apr 6 2001 /lib/libc-2.2.2.so
-rw-r--r--1 root root 26350254 Apr 6 2001 /usr/lib/libc.a
-rw-r--r--1 root root 178 Apr 6 2001 /usr/lib/libc.so
lrwxrwxrwx1 root root 10 Jul 28 17:44 /usr/lib/libc-client.a -
c-client.a
Configure command: ./configure --prefix=/usr/local/mysql4
--localstatedir=/usr/local/mysql4/data
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Two Tier Enviornment
Hi, I have Apache running on a solaris 7 on one box and mysql on another box. Im using a program called right now live (www.rightnow.com), which is a web-based FAQ and customer support tool. How can i get the box running apache pull data from the box running mysql? Any suggestions? Thank u - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Duplicate Inserts
Okay, so I did some detective work and found the problem. I thought you might want to know the cause. I use Textpad as my HTML editor right now, and when you create an HTML file from scratch, it has the following BODY tag: BODY BGCOLOR=#FF TEXT=#00 LINK=#FF VLINK=#80 ALINK=#FF00FF BACKGROUND=? The BACKGROUND=? part was causing the problem. When I remove ONLY that part of the body tag, the duplication problem went away. - Original Message - From: Eddie Heard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, October 20, 2001 12:05 AM Subject: Duplicate Inserts I'm running MySQL on a WIN2k server box. I'm doing inserts using PHP. Here's the code for the insert: $db = mysql_connect($hostname, $uid, $pwd); //$hostname is localhost and $uid/$pwd are userid and password mysql_select_db(heroes,$db); $insertQuery = INSERT INTO UniqueCardList (MCardID,OwnerID) VALUES(1,1); echo $insertQuery . BR\n; $insertResult = mysql_query($insertQuery,$db) or die (Insert query failed); I can go in, delete all records from the table, run the code once, and wind up with two records. Anybody got any ideas? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: finding the range of results?
Hi, I hope I get your question right. Your problem sounds like: Given the number of items per page, what page will an Id fall into The solution I propose is not very sophisticated but it does work for me. Say your table looks like Page 1 1Image A 2Image B 3Image C Page 1 Page 2 4Image D 5Image E 6Image F Page 2 Page 3 9Image G 10 Image H 12 Image I Page 3 Page 4 14 Image J 20 Image K 22 Image L Page 4 Page 5 30 Image M Page 5 Rule: 1 page consists of 3 images. Viewer initially clicks on Image H (Id 10) from Page 3, he then flips forward 3 images which now points to Image K (Id 20). At this time, he decideds to go back to the index page, which should now displays Page 4. The variable that you'll have to keep track with is the Image Id, which is 14 for this particular case. You would do a query: SELECT ceiling (count(*) / 3) FROM table WHERE ImageId = 14; result will be 4 You construct your query with limit like: LIMIT page * page_size, page_size which evaluates to LIMIT 4 * 3, 3 Regards, Jindo -Original Message- From: Michael [mailto:[EMAIL PROTECTED]] Sent: Saturday, October 20, 2001 12:47 PM To: [EMAIL PROTECTED] Subject: finding the range of results? Short version: How do you find what position a value is in a larger query's result? Long version: I have a thumbnail image viewing page that pages through search results using LIMIT page * page_size, page_size and that table does keep id's from all results but they are not always 1, 2, 3, 4, 5... often they are 1, 3, 6, 7, 9, etc.. in no special pattern but in order.. there is also an image id per each row that is not in order and again in no special pattern.. in cases where the user has clicked an image it uses the image id to view the image.. then when the user wants to return to browse mode it needs to figure out what page to return to. easy.. except they can also flip backwards and forward in the results in view mode so the page could change. So I need to find the page which I can do if I can find the id and it's position in the queries result. How exactly would I find the position in the results without dumping all results and using an external function. Something like position = variable in array. *^*^*^* Michael McGlothlin [EMAIL PROTECTED] http://mlug.missouri.edu/~mogmios/projects/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 resequencing...
Hi. This may be a obvious problem but... I want to re sequence a auto increment column called p_key starting with 1001. I found some information on this the Paul DuBois book MySql: I tried dropping the column and adding it again per there instructions: ALTER TABLE t DROP i; ALTER TABLE t ADD i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY; But It did not help as it does not start at 0 value but at the last value such as 1076 What do I need to do to get it to start again at 0 or at 1001? My mysql version is 3.23.38 running on redhat 6.2 Thanks, Mark _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to get the correct result -- Thrid Time --
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 19 Oct 2001, Clyde Jones wrote: Sorry, I forgot to answer your question about similar rows. Rows 5208 and 3063 are NOT the same (row 5208 has spaces and returns), besides your query asked for ALL rows not just the unique ones. Remember, computers only give you what you asked for, not what you want. Hmm... I learnt that 11 years ago :) Ok, I will tell you what I want. I want all unique rows. Now if there are spaces in like: | Abou Nawas Djerba | | Abou Nawas Djerba | To me they are same, but since there is a space, they are not. What I want is just one name If you want to get only unique, non-blank results then SELECT DISTINCT replace(replace(nome_hotel,\n,), , ) from hotel The problem is that the space ( ) will match if there are two spaces. I am not sure how may are there. Adrian On Fri, 19 Oct 2001, Adrian D'Costa wrote: | Date: Fri, 19 Oct 2001 10:43:31 +0530 (IST) | From: Adrian D'Costa [EMAIL PROTECTED] | To: Clyde Jones [EMAIL PROTECTED] | Subject: Re: how to get the correct result -- Thrid Time -- | | Hi, | | Thanks. It gave an error but it work after adding the \n. Now the | result is: | | +--+-+ | | id | replace(nome_hotel,\n,) | | +--+-+ | | 3825 | | | | 3827 | | | | 3391 | Abi d'Oru | | | 5208 | Abou Nawas Djerba | | | 3063 | Abou Nawas Djerba | | | 1252 | Adams Beach | | | 9757 | Aegean Village | | | 7973 | Aegean | | | 8917 | Aegean Village | | | 8122 | Aegeon | | +--+-+ | | If you notice id 5208 and 3063 are the same so also with 9757 and 7973. I | tried using trim it is the same. Any pointers. | | Thanks | | Adrian | | On Thu, 18 Oct 2001, Clyde Jones wrote: | | the doc page is here | http://www.mysql.com/doc/S/t/String_functions.html | try | | select replace(nome_hotel,\n,) | from hotel | group by nome_hotel limit 10; | -- They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety. - Benjamin Franklin Clyde Jones - http://www.clydec.net -- gpg: Warning: using insecure memory! gpg: Signature made Fri 19 Oct 2001 07:13:23 PM IST using DSA key ID AA5040FF gpg: Can't check signature: public key not found -- -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.2 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE70RsQ/f1mUNueu/oRAkaAAKCF+fvJuHkT0VBeggufv9J1PHQrXwCglcuu 7uCqVZa/IcZZb0n/clkvmlU= =VCTo -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
