Re: DELETE from more than one table
On Mon, 07 Jun 2004 12:36:47 + shaun thornburgh [EMAIL PROTECTED] wrote: Hi, Is it possible to DELETE from more than one table in one command? Thanks for your help DROP TABLE IF EXISTS table1, table2, table3; http://dev.mysql.com/doc/mysql/en/DROP_TABLE.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comma Delimited Import Error
David Blomstrom wrote: I just discovered that one of my MySQL tables is incomplete. I created it by importing a comma delimited file I had made from a spreadsheet, but it appears as many as 3,000 rows or so were missing. So I decided to give it another try. MySQL Front refused to import it because of some sort of error I haven't yet identified. Navicat imports it, logging roughly 80 errors. When I view the finished table, many of the entries in the first field are followed by ,. For example... al, tx, Here are a few rows from the comma delimited file I imported. Do you have a hunch what might be going on here? Thanks. gd,oat,239,,Grenada,nat gp,oat,240,,Guadeloupe,dep ht,oat,241,,Haiti,nat is,oat,242,,Iceland,nat jm,oat,243,,Jamaica,nat It would be better to post some of the lines that did not import. Posting a show create table would also help. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select double value
The problem is that in my java.sql.ResultSet.getDouble(strength) a zero amount throws a number format exception. Hi Paul, Not a Java user so I haven't tested this. You could try... SELECT abs( strength ) FROM data ; It 'might' stop the exception! Cheers Dean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select double value
Are you catching the error? Are you sure the value returned is what you are expecting? -Original Message- From: Dean Urmson To: 'Paul McNeil'; [EMAIL PROTECTED] Sent: 6/7/04 8:16 AM Subject: RE: Select double value The problem is that in my java.sql.ResultSet.getDouble(strength) a zero amount throws a number format exception. Hi Paul, Not a Java user so I haven't tested this. You could try... SELECT abs( strength ) FROM data ; It 'might' stop the exception! Cheers Dean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Initiate perl script in linux from a Windows VBA program
Thanks for the response. Does this mean that I would not use perl and only program in VBA? If I do need perl does it have to be installed on the Windows side? Where can I get some examples with ODBC in VBA? thanks. Mike Hillyer [EMAIL PROTECTED] wrote: Annie Law wrote: Hi,I would appreciate help with the following. I would like to be ableto initiate a perl script that is residing on a linux server from anExcel workbook. I am working on the Excel workbook in Windows. Iwould appreciate any pointers, sample code for accomplishing this inVBA or some other method.The perl script that I am trying to call will take the informationfrom a mysql database and update the Excel sheet.The script will also take information from the Excel sheet and updatethe database.thanks very much. - Post your free ad now! Yahoo! Canada Personals Why wouldn't you just use ODBC to connect to the server and work directly? Remotely initiating a perl script would involve creating a telnet or SSH session from within Excel using VBA, which would be a far more complex undertaking. -- Mike Hillyer, Technical Writer MySQL AB, www.mysql.com Office: +1 403-380-6535 The Open Source movement has become a major force across the software industry, and MySQL is the world's most popular open source database. --Fortune Magazine begin:vcard fn:Mike Hillyer n:Hillyer;Mike org:MySQL AB;Documentation Team adr:;;Canada email;internet:[EMAIL PROTECTED] title:Technical Writer tel;work:+1 403 686 tel;home:+1 403 380 6535 tel;cell:+1 403 894 6535 x-mozilla-html:FALSE url:http://www.mysql.com version:2.1 end:vcard - Post your free ad now! Yahoo! Canada Personals
Re: Backing Up a Database
Thanks, but I have innodb tables as well. I don't see any caveats in the docs for mysqldump, so I'm *assuming* it'll work correctly, but I'd like to hear from someone who is counting on it in a production environment, just to be sure. Thanks, Lou - Original Message - From: McKeever Chris [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 04, 2004 8:39 PM Subject: Re: Backing Up a Database On Fri, 4 Jun 2004 18:16 , Lou Olsten [EMAIL PROTECTED] sent: For the time I've been testing, I've used the procedures outlined in the help to take my backups, which entails doing a FLUSH TABLES WITH READ LOCK in my MySQL monitor, then going to a shell prompt and executing the mysqldump utility, then issuing the UNLOCK TABLES from my MySQL monitor. Now I'm trying to schedule all this and I have a question about using just mysqldump. If I use the --lock-tables parm, am I getting the same functionality? The reason I'm concerned is because the help says: The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup. I cant answer your question directly, but I suggest looking at mysqlhotcopy Can I make sure that happens without moving back and forth between the MySQL monitor and the mysqldump utility? Thanks, Lou --- Chris McKeever If you want to reply directly to me, please use cgmckeever--at--prupref---dot---com A href=http://www.prupref.com;www.prupref.com/A Prudential Preferred Properties A href=http://www.prupref.com;Chicago and Illinois NorthShore Real Estate Experts/A Prudential Preferred Properties www.prupref.com Success Driven By Results Results Driven By Commitment Commitment Driven By Integrity We Are Prudential Preferred Properties -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spatial extensions and GIS
In both cases you will have to write your own code. MySQL, so far, only can store Spatial data and do very simple (still, r-tree optimized) GIS queries (like: all objects inside rect). mirza Terry Pothecary wrote: Hi. This may be a daft question, so forgive me: I am just starting out with the Spatial extensions and I am wondering how I can use it to determine the distances between any 2 landmarks that have been entered as POINTs in the database. The POINTs are entered with longitude and lattitude coordinates. I have 2 problems: 1) The Distance nor the Buffer functions have not been implemented. 2) How can I use MySQL to help me convert from lat / long to distances in miles (or KM)? Thanks. Terry. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DELETE from more than one table
Hi Paul, Thanks for the reply. I actually need to delete data from about 10 tables, is this possible? From: Paul McNeil [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] Subject: RE: DELETE from more than one table Date: Mon, 7 Jun 2004 08:47:04 -0400 I believe you can use... Delete from TableA join TableB ON TableA.data = TableB.data WHERE TableA.otherData = myNeeds. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Monday, June 07, 2004 8:37 AM To: [EMAIL PROTECTED] Subject: DELETE from more than one table Hi, Is it possible to DELETE from more than one table in one command? Thanks for your help _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.2 regression failure: error reading communicaton packets
Description: I have just built 4.1.2 from source (in order to include OpenSSL), and find that the regression testing fails on the first test. The master.err contains the following: CURRENT_TEST: alias /usr/local/src/db/mysql-4.1.2-alpha/sql/mysqld: ready for connections. Version: '4.1.2-alpha-debug-log' socket: '/usr/local/src/db/mysql-4.1.2-alpha/mysql-test/var/tmp/master.sock' port: 9306 040607 18:25:01 Aborted connection 1 to db: 'test' user: 'root' host: `localhost' (Got an error reading communication packets) 040607 18:25:01 /usr/local/src/db/mysql-4.1.2-alpha/sql/mysqld: Normal shutdown 040607 18:25:01 /usr/local/src/db/mysql-4.1.2-alpha/sql/mysqld: Shutdown Complete User time 0.00, System time 0.01 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 116, Physical pagefaults 604, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0 How-To-Repeat: make test Fix: Unknown at this point. Submitter-Id: submitter ID Originator:Charles Bailey Organization: Children's Hospital of Philadelphia/University of Pennsylvania MySQL support: none Synopsis: communication error in regression testing Severity: critical Priority: medium Category: mysql Class: sw-bug Release: mysql-4.1.2-alpha (Source distribution) Server: /usr/local/src/db/mysql-4.1.2-alpha/client/.libs/lt-mysqladmin Ver 8.40 Distrib 4.1.2-alpha, for intel-linux on i686 Server version 4.1.2-alpha-debug-log C compiler:gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5) C++ compiler: gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5) Environment: System: Linux localhost 2.4.20-30.9smp #1 SMP Wed Feb 4 20:27:46 EST 2004 i686 athlon i386 GNU/Linux Architecture: i686 Some paths: /usr/local/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/3.2.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Thread model: posix gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5) Compilation info: CC='gcc' CFLAGS='-O3' CXX='gcc' CXXFLAGS='-O3 -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Dec 3 2003 /lib/libc.so.6 - libc-2.3.2.so -rwxr-xr-x1 root root 1561228 Nov 12 2003 /lib/libc-2.3.2.so -rw-r--r--1 root root 2332200 Nov 12 2003 /usr/lib/libc.a -rw-r--r--1 root root 204 Nov 12 2003 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Apr 26 2003 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--with-mysqld-user=mysql' '--without-innodb' '--enable-assembler' '--enable-local-infile' '--without-bench' '--without-berkeley-db' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-libwrap' '--with-vio' '--with-openssl' '--with-openssl-includes=/usr/include' '--with-openssl-libs=/usr/lib' '--with-debug' '--enable-thread-safe-client' 'CFLAGS=-O3' 'CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Endless primary key build
You're right - it is a data related issue. I finally made it work with the live data on high speed but in a way that puzzles me. Do you know the explanation ? (I'm using mySQL 4.1.1a-max-nt on Win2K) The index of the primary key is using the same data set (content and size) for both the test and live data. That was the reason I didn't get it why the performance dropped when using the live data. The difference was the order in which the rows were inserted into the table. When the order is right, the performance is high. The test data is programmatically generated in the ascending order of the primary key. The live data has no particular order. I did a simple test to reproduce this: * Create the table that will contain the live data. * Disable the table's keys * Load the live data using LOAD DATA INFILE ..., sorted in the random order. * Enable the table's keys * Run the unit tests = 2000 seconds * Dump that table's content into a text file, sorted on the ascending order of the primary key. * Create a second table identical to the first one. * disable keys/LOAD DATA INFILE new text file/enable keys * Run the same unit tests using the new table = 40 seconds It looks like the physical order of the rows has an influence on the way the primary key is building its index. Is this normal in mySQL ? Any idea ? Thanks, Jan. -Original Message- From: Tim Cutts [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 25, 2004 9:32 AM To: Jan Goyvaerts (jgoyvaer) Subject: Re: Endless primary key build On 24 May 2004, at 10:49 am, Jan Goyvaerts ((jgoyvaer)) wrote: My only conclusion is that getting the data first from another database does something to mySQL... The other difference is the data. Your test data was probably evenly distributed, or normally distributed, depending on your random number generator. The live data is probably distributed very differently. Maybe it's sparse. Maybe it's skewed or clustered in interesting ways. Either way, the one thing this will have a large impact on is the arrangement and performance of the index. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.2 myisamchk chokes on a fulltext
Hi. I'm having this problem on 4.1.2, when I run myisamchk -o on my table I get a bunch of these lines: Duplicate key 3 for record at 56134200 against new record at 244828223 then it segfaults. key 3 is a fulltext. I tried the same thing on the same data with 4.0.14 and it worked fine, I also tried dropping the fulltext and adding it again. myisamchk -e doesn't complain for some reason. Any help very appreciated /thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select double value
Paul McNeil wrote: Good morning to all. I have a problem with a workaround but I wanted to know if others have run into this. Are you sure the problem is with mysql? If so, which version do you have? I have 4.0.20, and I get different (expected) results. Table DATA Column strength [double] CREATE TABLE data (strength DOUBLE); INSERT INTO data VALUES (3.256498), (0), (2.71828), (NULL), (0.0); When I select strength from DATA and the result is a non zero amount it returns correctly 3.256498 however if it is a 0 amount I get 0. mysql SELECT strength FROM data; +--+ | strength | +--+ | 3.256498 | |0 | | 2.71828 | | NULL | |0 | +--+ 5 rows in set (0.18 sec) The problem is that in my java.sql.ResultSet.getDouble(strength) a zero amount throws a number format exception. SO, I placed a conditional SELECT CASE WHEN strength IS NULL OR strength = 0 THEN 0 ELSE strength END This, however seems to truncate the result so that a zero return results in 0 BUT a return of 3.1236564 results in 3. mysql SELECT CASE WHEN strength IS NULL OR strength = 0 - THEN 0 ELSE strength END AS strength - FROM data; +--+ | strength | +--+ | 3.256498 | |0 | | 2.71828 | |0 | |0 | +--+ 5 rows in set (0.27 sec) Bummer. Finally I had to restructure my conditional... SELECT CASE WHEN strength IS NOT NULL AND strength != 0 THEN strength ELSE '0' END You shouldn't need to quote 0, and IS NOT NULL is redundant here, as strength != 0 will evaluate to FALSE when strength is NULL. mysql SELECT CASE WHEN strength != 0 THEN strength ELSE 0 END strength - FROM data; +--+ | strength | +--+ | 3.256498 | |0 | | 2.71828 | |0 | |0 | +--+ 5 rows in set (0.27 sec) I feel that this type of data manipulation shouldn't need to be done. Is Agreed. this a bug or normal for a return type of double? For me, it is neither. Are you using an older version? Otherwise, I suspect the problem lies elsewhere. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stop query on first match
This doesn't quite make sense. You seem to say that several rows will match but then you say only one will. It must be one or the other. Perhaps I've misunderstood you. You also seem to imply that with BETWEEN you get a full table scan even though there is only one match for each row. That sounds like an indexing problem, but it is hard to say without more information. It would help us help you if you at least posted the query and the results of EXPLAIN. It would probably also help if you told us more about the tables, perhaps with SHOW CREATE TABLE. Michael Emmanuel van der Meulen wrote: Hello all, I see a similar question was asked before, but it was not answered. I hope someone can assist me. My query uses two tables. The query selects one row on table b for each row on table a, but uses between in the select. It can only ever return one row from table b, for each row on table a, due to the contents that is stored in table b. The table contains in excess of a million records. What happens as a result of the between is that for the query, several rows seem to be candidates on table b, but once the query evaluates and sifts through the candidate rows on table b, only one row will ever match. So if I could inform MySql to stop the query for the particular row, once one row on table b matches the row on table a, the query would return hundreds of times faster. As an experiment I took one example and used limit and the query reduced from 4 secs to .01 sec. However, when doing the 'live' query, I cannot use limit because, I do not want overall only 1 row returned, I want one row returned for each of the rows from table a which has 1 match each on table b. I've looked in several books and searched Google but cannot get a way of doing this. It seems Oracle has a 'FIRST' in their select which they use for such a use case. But I do not see anything for MySql anywhere. Could someone please assist me. Kind regards Emmanuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump causes slave to stop
Hello, I am dumping a single innodb DB using mysqldump. Everytime I do it, it crashes the slave thread. Below are the commands I'm using to dump [I also have tried --delete-master-logs command in the actual data dump too (second line) with the same problem.] I am willing to hear advice on the best place to put this. Note, I am cycling the bin-logs every fine minutes to provide a type -of point-in-time backup using 'mysqladmin flush-logs'. Thats why the binlog numbers are so high. I have the flush command NOT run if mysqldump is running. Important side-note: I have removed --quick from these as it was causing another innodb db on the same server to *appear* to LOCKUP during the backup of THIS db... ideas? Also. during this backup, all threads attached to the db go into Waiting for release of readlock - Is this normal? Master is 4.0.14, slave is 4.0.20... Slave was 4.0.14 and I still saw the problem. MASTER DUMP COMMANDS # safety first!!! make a daily backup of the XX schema! mysqldump --extended-insert --single-transaction --no-data --add-locks --compress --skip-lock-tables --delete-master-logs XX | bzip2 -c /mnt/data/backups/`date +%Y%m%d`/XX.SCHEMA.sql.bz2 # at one minute after midnight, fire off the backup mysqldump --single-transaction --add-locks --compress --skip-lock-tables --extended-insert --master-data XX | bzip2 -c /mnt/data/backups/`date +%Y%m%d`/XX.dump.sql.bz2 /MASTER DUMP COMMANDS SLAVE ERROR LOG 040605 0:09:20 Error reading packet from server: Could not open log file (server_errno=1236) 040605 0:09:20 Got fatal error 1236: 'Could not open log file' from master when reading data from binary log 040605 0:09:20 Slave I/O thread exiting, read up to log 'atlblg01-bin.262', position 714675 /SLAVE ERROR LOG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [mysql-php] mysqlimport error
is that a problem? I want to avoid ftp db.txt files and then mysqlimport them - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 02, 2004 4:38 PM Subject: Re: [mysql-php] mysqlimport error nikos [EMAIL PROTECTED] wrote: I'm trying mysqlimport --local -d --fields-enclosed-by=| --fields-terminated-by=; - -lines-terminted-by=\n -unikos -p mydb c://temp//programs.txt but i get an Error: File 'c:/temp/programs.txt' not found (Errcode: 2), when using table: programs What I'm doing wrong? RH-9 - APACHE v2 - mysql 3.23.54 Looks like you run mysqlimport on the server host but file is located on Windows box. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select double value
Paul McNeil wrote: Thank you very much for the response. According to MySQL Control Center the MySQL DB I am connecting to is version 4.0.17. Possibly that is the problem. You'll get quicker (and, on average, better) responses if you keep the thread on the list. I have mysql 4.0.17 running on another machine, so I tried this there and got the exact same results as I got on 4.0.20. If you haven't already, please try this directly in the mysql client to verify or rule out mysql as the culprit. Unless your 4.0.17 is somehow different than mine, I expect you'll find the problem must be occurring after the data leaves the server. Presumably, that would be either in the Java-MySQL interface or somewhere in your code. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Comma Delimited Import Error
On Jun 5, 2004, at 11:40 PM, David Blomstrom wrote: Here are a few rows from the comma delimited file I imported. Do you have a hunch what might be going on here? Thanks. gd,oat,239,,Grenada,nat gp,oat,240,,Guadeloupe,dep ht,oat,241,,Haiti,nat is,oat,242,,Iceland,nat jm,oat,243,,Jamaica,nat Do you see where you have two commas together such as 239,,Grenada If the field that lines up with the field between ,, does not allow NULL, then an error will occur. Either redefine the field to allow NULL, or use regex on that file to convert ,, to ,, or ,0, as appropriate. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Asterisks in Integer Columns
David, Daniel is exactly right. In general, it is not a good idea to try to cram two pieces of data into one column. Your questions at the end of your post are good examples of why you don't do it that way. With a population column and a separate population_note column, you can easily answer those questions. The population column is an integer column which only contains integers. If you don't want to show the notes, simply select and display the population values. If you do want to display the notes, then you select them as well. Once selected, you can display them as you see fit: an extra note column in table form, with superscripts which refer to footnotes, etc. If you try to put both in the same column, you won't have integers anymore, which will screw up sorting by population, will require note-stripping code when you don't want to display them, difficulty finding which rows have notes and which don't, and so on. In general, if it answers a different question, it goes in a separate column. Michael Daniel Clark wrote: One simple option would be to add a footnote column. And add in your code, if footnote column is NOT NULL then add a * on to population and show footnote at the bottom. population footnote 100 null 200*yada yada Suppose I have several columns of numerals - area, popoulation, etc. - and I want to include asterisks and footnotes, as in below: 200 4200 258* 234 24 258superscript1/superscsript 2400 What are some good strategies for doing this? You really aren't supposed to include asterisks in integer columns, right? It would also be nice to have a strategy that would give you the option of masking asterisks and footnotes when you don't want them displayed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT PRIVILEGES doesn't perform the way I expect it to....??
I'm having a little trouble with granting the correct privilege to the user with one database instead of all databases. When I do this with all databases, it work great.. --snip-- //Issued SQL Command... GRANT ALL ON *.* TO 'Username'@'localhost.domain.com' mailto:'Username'@'localhost.domain.com' IDENTIFIED BY 'Password' //Correct Data in user table in mysql database.. +---+-+--+-+-+-- ---+-+-+---+-+-- -+--+---++-+ ++--++-- -+--+--+-+-- +--++-+--+---+-- ---+-+ | Host | User| Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | +---+-+--+-+-+-- ---+-+-+---+-+-- -+--+---++-+ ++--++-- -+--+--+-+-- +--++-+--+---+-- ---+-+ | localhost | Username | 14617e5f1090a4a9 | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | N | Y | Y | Y | Y| Y | Y | Y | Y| Y | Y| | | | | 0 | 0 | 0 | +---+-+--+-+-+-- ---+-+-+---+-+-- -+--+---++-+ ++--++-- -+--+--+-+-- +--++-+--+---+-- ---+-+ --snip-- I don't want to this user to have access to most databases, just only one database. When I do this, it doesn't grant the priviledges the same way... --snip-- //Issued SQL Request GRANT ALL ON DATABASE_NAME.* TO 'Username'@'localhost.domain.com' mailto:'Username'@'localhost.domain.com' IDENTIFIED BY 'Password' //Incorrect Data in user table in mysql database.. | localhost | Username | 14617e5f1090a4a9 | N | N | N | N | N | N | N | N | N| N | N | N | N | N | N| N | N | N | N| N | N| | | | | 0 | 0 | 0 | --snip-- What do I need to do to make those 'Select', 'Update', 'Delete', 'Alter', etc.. to be Y by issuing that SQL Request from above??? What did I do wrong?? Thanks, Scott
RES: GRANT PRIVILEGES doesn't perform the way I expect it to....? ?
Hi Scott, Really in user table not modify nothing, but in db table the privileges appear ok. Your user get message of access denied? Regards, Renato Cramer. -Mensagem original- De: Scott Fletcher [mailto:[EMAIL PROTECTED] Enviada em: segunda-feira, 7 de junho de 2004 17:29 Para: [EMAIL PROTECTED]; [EMAIL PROTECTED] Assunto: GRANT PRIVILEGES doesn't perform the way I expect it to?? I'm having a little trouble with granting the correct privilege to the user with one database instead of all databases. When I do this with all databases, it work great.. --snip-- //Issued SQL Command... GRANT ALL ON *.* TO 'Username'@'localhost.domain.com' mailto:'Username'@'localhost.domain.com' IDENTIFIED BY 'Password' //Correct Data in user table in mysql database.. +---+-+--+-+-+-- ---+-+-+---+-+-- -+--+---++-+ ++--++-- -+--+--+-+-- +--++-+--+---+-- ---+-+ | Host | User| Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | +---+-+--+-+-+-- ---+-+-+---+-+-- -+--+---++-+ ++--++-- -+--+--+-+-- +--++-+--+---+-- ---+-+ | localhost | Username | 14617e5f1090a4a9 | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | N | Y | Y | Y | Y| Y | Y | Y | Y| Y | Y| | | | | 0 | 0 | 0 | +---+-+--+-+-+-- ---+-+-+---+-+-- -+--+---++-+ ++--++-- -+--+--+-+-- +--++-+--+---+-- ---+-+ --snip-- I don't want to this user to have access to most databases, just only one database. When I do this, it doesn't grant the priviledges the same way... --snip-- //Issued SQL Request GRANT ALL ON DATABASE_NAME.* TO 'Username'@'localhost.domain.com' mailto:'Username'@'localhost.domain.com' IDENTIFIED BY 'Password' //Incorrect Data in user table in mysql database.. | localhost | Username | 14617e5f1090a4a9 | N | N | N | N | N | N | N | N | N| N | N | N | N | N | N| N | N | N | N| N | N| | | | | 0 | 0 | 0 | --snip-- What do I need to do to make those 'Select', 'Update', 'Delete', 'Alter', etc.. to be Y by issuing that SQL Request from above??? What did I do wrong?? Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 2013 : Lost connection to MySQL server during query
Ok, it seems I fixed my problem before anybody was available to help me. The error was due to bad code -- the program was trying to send multiple queries through the same connection. Even though it was compiled as thread safe, you still have to find some way to prevent concurrent queries on the same connection. Anybody writing server-side applications using the C api, please beware of this issue. I can suggest a few workarounds if anybody else out there is having the same problem with their C program. Daniel Daniel Whitener wrote: Hello all, I'm getting the error message 2013 : Lost connection to MySQL server during query during inserts on a lightly-loaded mysql server. I also noticed the following error in the error log... Version: '4.0.20-standard-log' socket: '/tmp/mysql.sock' port: 3306 040603 16:25:48 Aborted connection 1 to db: 'milterdb' user: 'milteruser' host: `localhost' (Got an error reading communication packets) I am going nuts trying to fix this error! I've done some googling and the only fixes I've heard of are fixing name resolution errors. I'm thinking that has nothing to do with this setup since 1000s of other inserts/queries are fine. One second it is working, the next second it stops with this error. The application interfaces with a Sendmail mail server and archives all incoming/outgoing email traffic to the MySQL database. The source is available if anybody thinks my poor coding is the problem. Any help or ideas will be greatly appreciated. Thanks in advance! Daniel Whitener basic config info is below. I can provide more details if needed... I'm using suse 9.1 uranus:~ # uname -a Linux uranus 2.6.4-52-default #1 Wed Apr 7 02:08:30 UTC 2004 i686 i686 i386 GNU/Linux uranus:~ # mysql status; -- mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) Connection id: 15 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: less Using outfile: '' Server version: 4.0.20-standard-log Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 55 min 51 sec Threads: 2 Questions: 313 Slow queries: 1 Opens: 11 Flush tables: 1 Open tables: 5 Queries per second avg: 0.093 mysqld section of the my.cnf file... # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock wait_timeout = 7200 interactive_timeout = 28800 skip-locking key_buffer = 64M max_allowed_packet = 10M table_cache = 128 sort_buffer_size = 1M read_buffer_size = 1M net_buffer_length = 8K myisam_sort_buffer_size = 16M user = mysql thread_stack = 1M thread_cache = 8 query_cache_size = 16M thread_concurrency = 4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DELETE from more than one table
At 14:01 + on 06/07/2004, shaun thornburgh wrote about Re: DELETE from more than one table: Hi Paul, Thanks for the reply. I actually need to delete data from about 10 tables, is this possible? If the field in the tables is defined as a Foreign Key (with ON DELETE CASCADE), deleting the root key will do this (you can then insert it if you did not want to delete it but only the records that pointed at it). I do not know how helpful this is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT: Errors from Extra Spaces in Spreadsheets
At 22:33 -0700 on 06/06/2004, David Blomstrom wrote about OT: Errors from Extra Spaces in Spreadsheets: I've been having a tough time importing comma-delimited files into my database tables. I just discovered that most of the errors are similar to this one: 060 is not a valid integer value I checked my spreadsheet - Microsoft Works - and discovered that there's a space after many of my numerals. I'm not sure how to remove these spaces. I didn't see anything in the Help files. I could do a search and replace, but that would also wipe out spaces between words - like... TrippspaceCounty I just wondered if there's a simple trick anyone knows of for getting rid of spaces like these or preventing them in the first place. Thanks. Assuming that these are not the last fields in the record a simple Search/Replace operation in a Text Processor would handle this: Find = space, - note that space is an actual space not the word Replace = , I note you mention S/R but you need to be more specific on what you are searching for (as I listed above) and not just look for spaces. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump causes slave to stop
Hello, I am dumping a single innodb DB using mysqldump. Everytime I do it, it crashes the slave thread. Below are the commands I'm using to dump [I also have tried --delete-master-logs command in the actual data dump too (second line) with the same problem.] I am willing to hear advice on the best place to put this. Note, I am cycling the bin-logs every fine minutes to provide a type -of point-in-time backup using 'mysqladmin flush-logs'. Thats why the binlog numbers are so high. I have the flush command NOT run if mysqldump is running. Important side-note: I have removed --quick from these as it was causing another innodb db on the same server to *appear* to LOCKUP during the backup of THIS db... ideas? Also. during this backup, all threads attached to the db go into Waiting for release of readlock - Is this normal? Master is 4.0.14, slave is 4.0.20... Slave was 4.0.14 and I still saw the problem. MASTER DUMP COMMANDS # safety first!!! make a daily backup of the XX schema! mysqldump --extended-insert --single-transaction --no-data --add-locks --compress --skip-lock-tables --delete-master-logs XX | bzip2 -c /mnt/data/backups/`date +%Y%m%d`/XX.SCHEMA.sql.bz2 # at one minute after midnight, fire off the backup mysqldump --single-transaction --add-locks --compress --skip-lock-tables --extended-insert --master-data XX | bzip2 -c /mnt/data/backups/`date +%Y%m%d`/XX.dump.sql.bz2 /MASTER DUMP COMMANDS SLAVE ERROR LOG 040605 0:09:20 Error reading packet from server: Could not open log file (server_errno=1236) 040605 0:09:20 Got fatal error 1236: 'Could not open log file' from master when reading data from binary log 040605 0:09:20 Slave I/O thread exiting, read up to log 'atlblg01-bin.262', position 714675 /SLAVE ERROR LOG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Production release of MySql 4.1
Emmanuel van der Meulen wrote: Hello all, Does anyone have an estimate of when MySql 4.1 will be released for production. MySql.cm says soon. Please advise, would that likely be weeks/months? Kind regards Emmanuel Months. I'd say at least 6 months. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems maintaining large high-availability search
I have been maintaining a mod-perl/MySQL4 web search engine with about 3M records, which gains and drops about 100k records daily. It runs on a dual-Opteron 242 system with 8GB RAM, 15k SCSI driv, SUSE Professional for AMD64. It recently grew to this size (from about 1M records), and I am encountering some problems scaling. 1. The timeout is set to 5 min, because of the number of queries, there are a lot of unused http processes that linger with connections, and the only way to seeminly keep MySQL connections available is to keep timeouts short. However, I have updates that take over an hour at a time, and the 5 min timeout will kill the update process. If I change the timeout to 2 hours, Apache will eat up all the connections. Im a little confused at the behaviour. Any suggestions appreciated. It seems to me the only answer is to maintain a completely seperate MySQL server with a 2 hour timeout on the same box, hotcopy the database, do the updates, and hotcopy it back, which I would *really* prefer not to do. There has got to be an easier way - any suggestions? 2. The other problem I have is that because I am rotating so many records daily and the queries are so complex and the tables/indexes so large, I want to keep the db OPTIMIZEd, but running an OPTIMIZE TABLE on this database also takes over an hour and creates timeouts on queries. At the same time, I want to get the optimize done as soon as possible. Again, a separate server would solve this problem, but I am really struggling for a way to not do that - as much as I love MySQL, I really hate installing, configuring, maintaining parallel permissions on 2 servers. Especially since MySQL needs maintainence so infrequently, every time I need to configure it it is like learning the entire process all over again from scratch, and I always goof on some little detail that doesnt raise its ugly head right away. If there is another option ( or maybe I shouldnt be OPTIMIZING every day?), suggestions appreciated. PS I do not have any problems with the query performance.
Re: Problems maintaining large high-availability search
Hi, 1. The timeout is set to 5 min, because of the number of queries, there are a lot of unused http processes that linger with connections, and the only way to seeminly keep MySQL connections available is to keep timeouts short. What about using a connection-pool? Like Apache::DBI. It should solve most of your problems (in theory...) However, I have updates that take over an hour at a time, and the 5 min timeout will kill the update process. If I change the timeout to 2 hours, Apache will eat up all the connections. Im a little confused at the behaviour. Probably annoying, but not confusing behaviour - if I understand your problem correctly. Any suggestions appreciated. It seems to me the only answer is to maintain a completely seperate MySQL server with a 2 hour timeout on the same box, hotcopy the database, do the updates, and hotcopy it back, which I would *really* prefer not to do. There has got to be an easier way - any suggestions? This doesn't sound a very nice solution. 2. The other problem I have is that because I am rotating so many records daily and the queries are so complex and the tables/indexes so large, I want to keep the db OPTIMIZEd, but running an OPTIMIZE TABLE on this database also takes over an hour and creates timeouts on queries. At the same time, I want to get the optimize done as soon as possible. IMHO you'd be better off by optimizing your databases so that an OPTIMIZE command would not be needed at all. I think you should change tha application instead of hacking MySql. (Been there, done that.) Probably you should place a layer between Apache and MySQL. Or do the slow queries in a spearate process (definetely not in Apache). Or separate the time slow queries into smaller ones. - Cs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Idea to speed up multiple jdbc connections?
Hi, I would appreciate any help on this: I got approximate same timing on following two: case 1: create 1 jdbc connection and issue 4 queries sequentially case 2: create 4 jdbc connections and issue 4 queries via 4 different threads at the same time The timing is done around statement.execQuery(query), so overhead of multithreading can be ignored. I would think case 2 should be faster, but it was not. Any idea? Thanks a lot PS: the mysqld server 4.1.1a is running with 16 threads __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with MySQL 4.0.20
Thank you very much for your bug report! And sorry if I doubted your report at the beginning; I hadn't thought of the rpm script. No problem. I sometimes get bug reports that I know are impossible! Yet they weren't. This one I would have barely noticed if it had not knocked the slaves all offline. Mysql query just in case. -steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems maintaining large high-availability search
1. The timeout is set to 5 min, because of the number of queries, there are a lot of unused http processes that linger with connections, and the only way to seeminly keep MySQL connections available is to keep timeouts short. What about using a connection-pool? Like Apache::DBI. It should solve most of your problems (in theory...) Why not just use a master-slave system and then you wouldn't have to worry about it. All inserts go off the master, and all selects on the slave. However, I have updates that take over an hour at a time, and the 5 min timeout will kill the update process. If I change the timeout to 2 hours, Apache will eat up all the connections. Im a little confused at the behaviour. Probably annoying, but not confusing behaviour - if I understand your problem correctly. An hour is not bad, I just had one that took right at 97 hours that finally finished. With a master-slave system you wouldn't have to worry about this problem anymore. Any suggestions appreciated. It seems to me the only answer is to maintain a completely seperate MySQL server with a 2 hour timeout on the same box, hotcopy the database, do the updates, and hotcopy it back, which I would *really* prefer not to do. There has got to be an easier way - any suggestions? This doesn't sound a very nice solution. Same as above, but an even easier solution is to not delete the records you don't need anymore. Just add a status flag, and update the flag to deleted or whatever. And change your selects to only look for ones that aren't deleted. Then you never have to optimize again. (In theory). 2. The other problem I have is that because I am rotating so many records daily and the queries are so complex and the tables/indexes so large, I want to keep the db OPTIMIZEd, but running an OPTIMIZE TABLE on this database also takes over an hour and creates timeouts on queries. At the same time, I want to get the optimize done as soon as possible. IMHO you'd be better off by optimizing your databases so that an OPTIMIZE command would not be needed at all. See above. I think you should change tha application instead of hacking MySql. (Been there, done that.) Probably you should place a layer between Apache and MySQL. Or do the slow queries in a spearate process (definetely not in Apache). Or separate the time slow queries into smaller ones. IMHO the first thing I would do is add a status flag. And see how that works. Then I would go to master-slave replication system if the flag option doesn't work as well as it should. It works for us without any problems. Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Idea to speed up multiple jdbc connections?
AFAIK, creation of connection from DB is expensive. This is one of the reasons why we need connection pooling. Best Regards, Jonathan Chiu OOCL Logistics Unit 1, 4/F., Sun Hung Kai Centre, 30 Harbour Road, Wanchai TEL: 852 . 2990 0174 FAX: 852 . 28249017 -Original Message- From: Haitao Jiang [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 08, 2004 8:14 AM To: mysql Subject: Idea to speed up multiple jdbc connections? Hi, I would appreciate any help on this: I got approximate same timing on following two: case 1: create 1 jdbc connection and issue 4 queries sequentially case 2: create 4 jdbc connections and issue 4 queries via 4 different threads at the same time The timing is done around statement.execQuery(query), so overhead of multithreading can be ignored. I would think case 2 should be faster, but it was not. Any idea? Thanks a lot PS: the mysqld server 4.1.1a is running with 16 threads __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] IMPORTANT NOTICE Email from OOCL is confidential and may be legally privileged. If it is not intended for you, please delete it immediately unread. The internet cannot guarantee that this communication is free of viruses, interception or interference and anyone who communicates with us by email is taken to accept the risks in so doing. Without limitation, OOCL and its affiliates accept no liability whatsoever and howsoever arising in connection with the use of this email. Under no circumstances shall this email constitute a binding agreement to carry or for provision of carriage services by OOCL, which is subject to the availability of carrier's equipment and vessels and the terms and conditions of OOCL's standard bill of lading which is also available at http://www.oocl.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
delete record that passed 1 day
Hi all, I have mysql table that has a field named 'crea_dt' (it records the creation date of data)... I want to delete records that have passed for more than a day description like this : if now()-crea_dt 0 then delete But I just can't find the suitable mysql command for this function. Anyone can help me, thanks a lot. By regard; Sukanto
Re: Idea to speed up multiple jdbc connections?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: AFAIK, creation of connection from DB is expensive. This is one of the reasons why we need connection pooling. Jonathan, While that might be true for other databases, it's not true for MySQL (connections are a few ms. to create). The real reason to use connection pooling is as a resource limiter so that you do not waste MySQL server-side resources for threads that are effectively doing nothing. Haitao's issue might be due to some locking in the database server, thus effectively serializing his four connections, or he might not be actually producing enough load to actually be able to measure any difference between his two approaches. If he could post his DDL, the relative size(s) of his data set(s) and the queries, that would be somewhere to start. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAxRgLtvXNTca6JD8RAjiSAJ0R5b6MNW0SdY5z4eJtmfgAV0ZMtgCgtGyn 037apgXT972UAR3Khkg7ITI= =4bja -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delete record that passed 1 day
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 07 June 2004 08:36 pm, starofframe wrote: description like this : if now()-crea_dt 0 then delete select date_sub(crea_dt, interval 1 day) - -- If you're like me, and I know *I* am... -- Joel Robinson -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAxRymld4MRA3gEwYRAubCAKCZQMtmxe04LtcOAND/u1I7GJOYtQCfdj1/ 48Ab3kxyB3zrYmZsudqgXvQ= =G+Ew -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delete record that passed 1 day
Hi Jeff, you mean I can use sql like this DELETE FROM table where date_sub(crea_dt, interval 1 day) thanks a lot Sukanto - Original Message - From: Jeff Smelser [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 08, 2004 8:55 AM Subject: Re: delete record that passed 1 day -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 07 June 2004 08:36 pm, starofframe wrote: description like this : if now()-crea_dt 0 then delete select date_sub(crea_dt, interval 1 day) - -- If you're like me, and I know *I* am... -- Joel Robinson -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAxRymld4MRA3gEwYRAubCAKCZQMtmxe04LtcOAND/u1I7GJOYtQCfdj1/ 48Ab3kxyB3zrYmZsudqgXvQ= =G+Ew -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delete record that passed 1 day
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 07 June 2004 09:25 pm, starofframe wrote: Hi Jeff, you mean I can use sql like this DELETE FROM table where date_sub(crea_dt, interval 1 day) Add: crea_dt - -- Anything worth fighting for is worth fighting dirty for. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAxSLzld4MRA3gEwYRAubzAKCkx8ybJNMBnbcYNChM9dG74HiEFgCfUzQk nffp4gZJFgbZXUl+qDm5s/0= =UzBy -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delete record that passed 1 day
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 07 June 2004 09:22 pm, Jeff Smelser wrote: On Monday 07 June 2004 09:25 pm, starofframe wrote: Hi Jeff, you mean I can use sql like this DELETE FROM table where date_sub(crea_dt, interval 1 day) Add: crea_dt Could be .. Damn, I always have to do a select first.. I get them backwards some times. - -- In nature, there are no rewards or punishments, only consequences. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAxSdFld4MRA3gEwYRAqvaAJ956TjRnblPfJLMWw3pUIiQV+ba4wCfbeVy o56hnpxuB5FnVqGmPMYhbC0= =um6f -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delete record that passed 1 day
Yes, I did found out just now... but delete from table where date_sub(crea_dt, interval 1 day)crea_dt should delete all data right??? All I want is compared to date_now eg : now is 2004-6-8 and all I want to delete is data that hav passed 1 or more days... such as L 2004-6-7,2004-6-6, etc.. so what's the suitable command then.. again thanks a lot Sukanto - Original Message - From: Jeff Smelser [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 08, 2004 9:41 AM Subject: Re: delete record that passed 1 day -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 07 June 2004 09:22 pm, Jeff Smelser wrote: On Monday 07 June 2004 09:25 pm, starofframe wrote: Hi Jeff, you mean I can use sql like this DELETE FROM table where date_sub(crea_dt, interval 1 day) Add: crea_dt Could be .. Damn, I always have to do a select first.. I get them backwards some times. - -- In nature, there are no rewards or punishments, only consequences. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAxSdFld4MRA3gEwYRAqvaAJ956TjRnblPfJLMWw3pUIiQV+ba4wCfbeVy o56hnpxuB5FnVqGmPMYhbC0= =um6f -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delete record that passed 1 day
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 07 June 2004 10:03 pm, starofframe wrote: but delete from table where date_sub(crea_dt, interval 1 day)crea_dt should delete all data right??? All I want is compared to date_now eg : now is 2004-6-8 and all I want to delete is data that hav passed 1 or more days... such as L 2004-6-7,2004-6-6, etc.. Here: http://dev.mysql.com/doc/mysql/de/Date_and_time_functions.html I gave you the function.. You can figure out how more to use it. - -- I don't have an attitude problem--it's supposed to be like this. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAxS8Mld4MRA3gEwYRAvWYAJ96EPnYuO79xu2BN1evRTD1KqLoJgCgkqJy J2jKgv4pvvc+7ImNlX7+qzc= =1DL5 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql memory optimization - AMD 64 - odd crashes
I just started setting up a dual opteron system with Suse 9.1 (x86_64). At this point, I am sticking with the Suse provided rpms. I am having some odd mysql restarts (crashed with immediate restart by safe_mysqld). Odd part about it: not a single line in the error log. The connections just drop and come back as the client tries to reconnect. One possible issue I figures is the way I try to use memory. Does MySQL allow to allocate more then 4 GByte of RAM? Right now, I am using 4 GByte for key-cache alone. So it doesn't complain, and appears to use about 5 GByte at the time it crashes (the machine has 16 GByte RAM). But does it cause instability to use that much key-cache? -- Johannes Ullrich [EMAIL PROTECTED] pgp key: http://johannes.homepc.org/PGPKEYS contact: http://johannes.homepc.org/contact.htm There are two kinds of system administrators: The first kind solves problems with little shell scripts. The second kind are the problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Idea to speed up multiple jdbc connections?
Yes. The time I measure like I said is purely around statement.execQuery() call. Connection creation is not a factor here at all. My database has 1.64 million rows and 4 queries are all selects, which are identical in both serial and parallel cases. In serial cases: Query 0 took 590 Query 1 took 431 Query 2 took 461 Query 3 took 440 In parallel cases: Queryer 3 query took 1552 Queryer 1 query took 1632 Queryer 2 query took 1783 Queryer 0 query took 1923 I don't understand why in 4 concurrent connection cases (already created not included in the timing) it takes more than 3 times longer to exec. a query. Thanks Haitao --- Mark Matthews [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: AFAIK, creation of connection from DB is expensive. This is one of the reasons why we need connection pooling. Jonathan, While that might be true for other databases, it's not true for MySQL (connections are a few ms. to create). The real reason to use connection pooling is as a resource limiter so that you do not waste MySQL server-side resources for threads that are effectively doing nothing. Haitao's issue might be due to some locking in the database server, thus effectively serializing his four connections, or he might not be actually producing enough load to actually be able to measure any difference between his two approaches. If he could post his DDL, the relative size(s) of his data set(s) and the queries, that would be somewhere to start. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAxRgLtvXNTca6JD8RAjiSAJ0R5b6MNW0SdY5z4eJtmfgAV0ZMtgCgtGyn 037apgXT972UAR3Khkg7ITI= =4bja -END PGP SIGNATURE- __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delete record that passed 1 day
Jeff Smelser wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 07 June 2004 10:03 pm, starofframe wrote: but delete from table where date_sub(crea_dt, interval 1 day)crea_dt should delete all data right??? All I want is compared to date_now eg : now is 2004-6-8 and all I want to delete is data that hav passed 1 or more days... such as L 2004-6-7,2004-6-6, etc.. Here: http://dev.mysql.com/doc/mysql/de/Date_and_time_functions.html I gave you the function.. You can figure out how more to use it. If crea_dt is a DATE, there's no sense comparing it to NOW(). Use CURDATE() instead. If crea_dt is a DATETIME, use CURDATE() or NOW() according to your specifications. For example, the following will delete rows dated yesterday or earlier: DELETE FROM table WHERE crea_dt CURDATE(); This next will delete rows from the day before yesterday or earlier: DELETE FROM table WHERE crea_dt CURDATE() - INTERVAL 1 DAY; If crea_dt is a DATETIME, and you want to select/delete rows as soon as they are over 24 hours old, use: DELETE FROM table WHERE crea_dt NOW() - INTERVAL 1 DAY; On a separate note, you generally shouldn't put the column in a function in your WHERE clause, as it prevents the use of an index on that column. Rather, compare the column to a function which evaluates to a constant. In other words, WHERE crea_dt NOW() - INTERVAL 1 DAY can use an index on crea_dt, but WHERE crea_dt + INTERVAL 1 DAY NOW() cannot. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Idea to speed up multiple jdbc connections?
Oppz! Sorry for overlooking your timing method. In this case, I believe if you run the query in four different machines at the same time, the statistics should almost the same as running four consecutive queries in the same machine. I believe the multi-threading implemented in the JVM and the OS is not parallelly the same! Best Regards, Jonathan Chiu OOCL Logistics Unit 1, 4/F., Sun Hung Kai Centre, 30 Harbour Road, Wanchai TEL: 852 . 2990 0174 FAX: 852 . 28249017 -Original Message- From: Haitao Jiang [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 08, 2004 12:06 PM To: Mark Matthews; JONATHAN CHIU (ISD-OLAPL/HKG) Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Idea to speed up multiple jdbc connections? Yes. The time I measure like I said is purely around statement.execQuery() call. Connection creation is not a factor here at all. My database has 1.64 million rows and 4 queries are all selects, which are identical in both serial and parallel cases. In serial cases: Query 0 took 590 Query 1 took 431 Query 2 took 461 Query 3 took 440 In parallel cases: Queryer 3 query took 1552 Queryer 1 query took 1632 Queryer 2 query took 1783 Queryer 0 query took 1923 I don't understand why in 4 concurrent connection cases (already created not included in the timing) it takes more than 3 times longer to exec. a query. Thanks Haitao --- Mark Matthews [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: AFAIK, creation of connection from DB is expensive. This is one of the reasons why we need connection pooling. Jonathan, While that might be true for other databases, it's not true for MySQL (connections are a few ms. to create). The real reason to use connection pooling is as a resource limiter so that you do not waste MySQL server-side resources for threads that are effectively doing nothing. Haitao's issue might be due to some locking in the database server, thus effectively serializing his four connections, or he might not be actually producing enough load to actually be able to measure any difference between his two approaches. If he could post his DDL, the relative size(s) of his data set(s) and the queries, that would be somewhere to start. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAxRgLtvXNTca6JD8RAjiSAJ0R5b6MNW0SdY5z4eJtmfgAV0ZMtgCgtGyn 037apgXT972UAR3Khkg7ITI= =4bja -END PGP SIGNATURE- __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ IMPORTANT NOTICE Email from OOCL is confidential and may be legally privileged. If it is not intended for you, please delete it immediately unread. The internet cannot guarantee that this communication is free of viruses, interception or interference and anyone who communicates with us by email is taken to accept the risks in so doing. Without limitation, OOCL and its affiliates accept no liability whatsoever and howsoever arising in connection with the use of this email. Under no circumstances shall this email constitute a binding agreement to carry or for provision of carriage services by OOCL, which is subject to the availability of carrier's equipment and vessels and the terms and conditions of OOCL's standard bill of lading which is also available at http://www.oocl.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delete record that passed 1 day
Thanks a lot Mike... what's a clear explanation... Actually I have used WHERE crea_dt + interval 1 day curdate() before I read ur reply ...but now I guess I better change it to WHERE crea_dtcurdate-interval 1 day again thanks a lot... and thanks to Jeff too. Sukanto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Idea to speed up multiple jdbc connections?
On Mon, 7 Jun 2004, Haitao Jiang wrote: Yes. The time I measure like I said is purely around statement.execQuery() call. Connection creation is not a factor here at all. My database has 1.64 million rows and 4 queries are all selects, which are identical in both serial and parallel cases. In serial cases: Query 0 took 590 Query 1 took 431 Query 2 took 461 Query 3 took 440 In parallel cases: Queryer 3 query took 1552 Queryer 1 query took 1632 Queryer 2 query took 1783 Queryer 0 query took 1923 I don't understand why in 4 concurrent connection cases (already created not included in the timing) it takes more than 3 times longer to exec. a query. Umh... if your queries are limited by some bottleneck on the server (such as, for example, CPU) then why would running them in parallel make it any faster? It seems that in the sequential case they are taking a total of 1922 (whatever those units are) while in the parallel case they are taking 1923. What this is telling you is that, in this case, a single query is able to fully utilize the resources (likely CPU given these numbers, although it is possible it could be disk) on the server. If a single query can fully utilize the server, all that adding more concurrency can possibly do is slow the total throughput down. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delete record that passed 1 day
starofframe wrote: Thanks a lot Mike... what's a clear explanation... You're welcome. Actually I have used WHERE crea_dt + interval 1 day curdate() before I read ur reply ...but now I guess I better change it to WHERE crea_dtcurdate-interval 1 day That's probably just a typo, but just in case, you want WHERE crea_dtcurdate-interval 1 day ( instead of ). again thanks a lot... and thanks to Jeff too. Sukanto Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld_multi
Hi, Can someone tell me how I can use mysqld_multi to have one RPM installation of MySQL(default with RedHat9) to run multiple mysqlds? Thanks -Minuk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Boolean mode, Fulltext,utf8
Hi All! Does works fulltext index on utf8 columns on boolean mode correctly on MySql 4.1.2 ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisampack, check: already compressed ?
Hello * Question: Is there a (cheap) way to find out if a MyISAM Table is already compressed, before running a myisampack on it ? Goal: -In a script that regularly compresses Tables i'd like to check before packing. In case the script has previously been started and already done the compression. TIA Micha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Production release of MySql 4.1
Hello all, Does anyone have an estimate of when MySql 4.1 will be released for production. MySql.cm says soon. Please advise, would that likely be weeks/months? Kind regards Emmanuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Configure MySQL on Jboss 3.2.3
Does anyone know how to configure MySQL on JBoss 3.2.3? This is what I have done: (i) copy /jboss3.2.3/docs/examples/jca/mysql-ds.xml to jboss3.2.3/server/default/deploy/ where ECPerfDS is the dtabase jndi name: ++ datasources local-tx-datasource jndi-nameECPerfDS/jndi-name connection-urljdbc:mysql://10.100.22.86:3306/ecperf/connection-url driver-classorg.gjt.mm.mysql.Driver/driver-class user-nameroot/user-name passwordintel1/password /local-tx-datasource /datasources (ii) Add jdbc driver mysql-connector-java-3.0.14-production-bin.jar into /jboss/server/default/lib (iii) Modify standardjaws in /jboss3.2.3/server/default/config as follows: jaws datasourcejava:/MySqlDS/datasource type-mappingmySQL/type-mapping (iv) Modify standardjbosscmp-jdbc.xml as: +++ defaults datasourcejava:/MySqlDS/datasource datasource-mappingmySQL/datasource-mapping +++ (v) Add the following into login-config.xml application-policy name = MySqlDSRealm authentication login-module code = org.jboss.resource.security.ConfiguredIdentityLoginModule flag = required module-option name = principalroot/module-option module-option name = userNameroot/module-option module-option name = passwordx/module-option module-option name = managedConnectionFactoryNamejboss.jca:service=LocalTxCM,name=MySqlDS/module-option /login-module /authentication /application-policy + (Vi) Copy mysql-jdbc2-service.xml from jboss-3.2.3/docs/examples/jms into jboss-3.2.3/server/default/deploy/jms (vii) grant previleges to mysql database: Grant all privileges on ecperf.* to 'root'@'10.100.22.86' identified by 'x' with grant option ecperf is the database created. But unfortunately, with what I have done, the connection seems to have problems with the following errors: com.sun.ecperf.webbeans.OtherException: Finder Exception occured for the request. at com.sun.ecperf.webbeans.ProcessListBean.getItemsList(ProcessListBean.java:157) at org.apache.jsp.new_order_jsp._jspService(new_order_jsp.java:263) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:210) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:220) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.jboss.web.tomcat.tc4.statistics.ContainerStatsValve.invoke(ContainerStatsValve.java:76) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2417) at
innoDB
Hi, Anybody know how do I know if innoDB is enabled? I have MySQL 3.23 installed. The MySQL document says the simplest way to install MySQL-Max is to replace the executable mysqld server with the corresponding executable from the MySQL-Max distribution. Where can I get the mysqld of MySQL-Max? Is MySQL-MAX the MaxDB on the download page? Thanks, Joseph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innoDB
[EMAIL PROTECTED] wrote: Anybody know how do I know if innoDB is enabled? SHOW VARIABLES LIKE have_innodb; If InnoDB is enabled, you will see 'YES' in the output. I have MySQL 3.23 installed. The MySQL document says the simplest way to install MySQL-Max is to replace the executable mysqld server with the corresponding executable from the MySQL-Max distribution. Where can I get the mysqld of MySQL-Max? How did you install MySQL? Is MySQL-MAX the MaxDB on the download page? No. Mysql-max packages you can find at: http://dev.mysql.com/downloads/mysql/3.23.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FreeBSD 5.x
Hi. SQL I use mysql-4.0.X under FreeBSD-5.2.1 There is no problem with libc_r. But under heavy loading with kse mysqld doesn't shutdown. This problem isn't only with kse. It was discussed some weeks ago. And if I remember it is exist with any other pthreads and under linux too. With kse mysql do concurrent selects more faster on MyISAM tables. For example: the same 2 selects with kse - 11 sec the same 2 selects with c_r - ~18 sec Peter wrote: | Hi, | Thanks for the reply :-)) | Any corruptions on MyIsam tables ? Cause in my case tables seems to | crash a lot on the freebsd machines, processor almost die on 4.x ... | Nothing like this. I have a lot of MyISAM tables and a view InnoDB Tables. There was no crash since April. So long... Gerrit -- beine computer support f?r open source software seumestrasse 82 08525 plauen fon: 0049 3741 404 837 fax: 0049 3741 404 839 gsm: 0049 178 279 48 32 mail: [EMAIL PROTECTED] url: http://www.beine-computer.de icq: 123851585 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Output from gpg gpg: /home/kota/.gnupg/options:112: ?? ? ? honor-http-proxy gpg: ??? keyserver-options honor-http-proxy ?? gpg: ???: ??! gpg: ??? ?? ?? ??. http://www.gnupg.org/faq.html gpg: ??? ??? ???, 6 2004 ?. 16:09:38 EEST ?? DSA ? ID 663AF798 gpg: ?? ? ???: ?? ?? Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't start server: Bind on TCP/IP port: Address already in use
Hi! I have restarted the Linux server. Typing mysqld result: 040607 11:32:47 Can't start server: Bind on TCP/IP port: Address already in use 040607 11:32:47 Do you already have another mysqld server running on port: 3306 ? 040607 11:32:47 Aborting 040607 11:32:47 mysqld: Shutdown Complete This isn't correct I think, as ps yields: PID TTY TIME CMD 9855 pts/100:00:00 bash 10363 pts/100:00:00 ps No sign of another mysql daemon running there. What to do? It used to work flawlessly. Am I hacked? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
key_buffer or key_buffer_size
Hi, Can somebody tell me if 'key_buffer' and 'key_buffer_size' are the same variables ? Perhaps it is just a question of syntax, I mean, in the my.cnf you write key_buffer=100M or you write set-variable = key_buffer_size=100M Same question for sort_buffer and sort_buffer_size, thread_cache and thread_cache_size, innodb_buffer_pool and innodb_buffer_pool_size etc. -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) email: mailto:[EMAIL PROTECTED] Tel : 04 72 69 42 18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
URGENT !! InnoDB Constraint problem !
i dont have that section in my status report (ill tried it before) here's the report *** 1. row *** Status: = 040607 10:04:41 INNODB MONITOR OUTPUT = Per second averages calculated from the last 5 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 24, signal count 24 Mutex spin waits 4, rounds 41, OS waits 0 RW-shared spins 48, OS waits 24; RW-excl spins 1, OS waits 0 TRANSACTIONS Trx id counter 0 22817 Purge done for trx's n:o 0 22815 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: FILE I/O I/O thread 0 state: waiting for i/o request I/O thread 1 state: waiting for i/o request I/O thread 2 state: waiting for i/o request I/O thread 3 state: waiting for i/o request Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 78 OS file reads, 157 OS file writes, 94 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 0, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 1345367 Log flushed up to 0 1345367 Last checkpoint at 0 1345367 0 pending log writes, 0 pending chkp writes 53 log i/o's done, 0.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 16796660; in additional pool allocated 673536 Buffer pool size 512 Free buffers 473 Database pages 38 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 38, created 0, written 103 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool activity since the last printout -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue; main thread: waiting for server activity Number of rows inserted 6, updated 0, deleted 5, read 1264 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s END OF INNODB MONITOR OUTPUT On 06 Jun 2004, at 16:37, Josh Trutwin wrote: On Sun, 6 Jun 2004 15:48:37 +0200 Dimitri Bosiers [EMAIL PROTECTED] wrote: snip INSERT INTO pages(hidden,name,page_number,data,is_section_root,layout_id,group_ id) VALUES(1,'nieuwe pagina',100,'',2,1,@LID); this fails with a ERROR 1216: Cannot add a child row: a foreign key constraint fails Run: SHOW INNODB STATUS; And post the results in the Latest Foreign Key Error section. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spatial extensions and GIS
Hi. This may be a daft question, so forgive me: I am just starting out with the Spatial extensions and I am wondering how I can use it to determine the distances between any 2 landmarks that have been entered as POINTs in the database. The POINTs are entered with longitude and lattitude coordinates. I have 2 problems: 1) The Distance nor the Buffer functions have not been implemented. 2) How can I use MySQL to help me convert from lat / long to distances in miles (or KM)? Thanks. Terry. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Can't start server: Bind on TCP/IP port: Address already in use
Hi Tobias Got the same problem on my local machine. Try @ps -aux And kill all mysql stuff manually Worked for me Cheers jan -Ursprüngliche Nachricht- Von: Tobias Bohlin [mailto:[EMAIL PROTECTED] Gesendet: Montag, 07. Juni 2004 11:30 An: [EMAIL PROTECTED] Betreff: Can't start server: Bind on TCP/IP port: Address already in use Hi! I have restarted the Linux server. Typing mysqld result: 040607 11:32:47 Can't start server: Bind on TCP/IP port: Address already in use 040607 11:32:47 Do you already have another mysqld server running on port: 3306 ? 040607 11:32:47 Aborting 040607 11:32:47 mysqld: Shutdown Complete This isn't correct I think, as ps yields: PID TTY TIME CMD 9855 pts/100:00:00 bash 10363 pts/100:00:00 ps No sign of another mysql daemon running there. What to do? It used to work flawlessly. Am I hacked? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] Can't start server: Bind on TCP/IP port: Address already in use
Tobias Bohlin wrote: I have restarted the Linux server. Typing mysqld result: 040607 11:32:47 Can't start server: Bind on TCP/IP port: Address already in use 040607 11:32:47 Do you already have another mysqld server running on port: 3306 ? 040607 11:32:47 Aborting You don't ever start MySQL like that. That's what the init scripts are for, specially since it needs to do some sanity checks (like the data directory check and preferences check.) This isn't correct I think, as ps yields: PID TTY TIME CMD 9855 pts/100:00:00 bash 10363 pts/100:00:00 ps No sign of another mysql daemon running there. 'ps' will only show you your current running tasks for your session. Try 'ps -A' to see all running tasks on your system. As a side note, please read 'man ps' for all the possible options. -- W | I haven't lost my mind; it's backed up on tape somewhere. + Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130 IT Director / SysAdmin / WebSmith . 800.441.3873 x130 Photo Craft Laboratories, Inc.. 3550 Arapahoe Ave. #6 http://www.pcraft.com . . .. Boulder, CO 80303, U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can't start server: Bind on TCP/IP port: Address already in use
You are only listing processes owned by you. ps -e will show all processes, add -f for a full listing. -Original Message- From: Tobias Bohlin [mailto:[EMAIL PROTECTED] Sent: 07 June 2004 11:30 To: [EMAIL PROTECTED] Subject: Can't start server: Bind on TCP/IP port: Address already in use Hi! I have restarted the Linux server. Typing mysqld result: 040607 11:32:47 Can't start server: Bind on TCP/IP port: Address already in use 040607 11:32:47 Do you already have another mysqld server running on port: 3306 ? 040607 11:32:47 Aborting 040607 11:32:47 mysqld: Shutdown Complete This isn't correct I think, as ps yields: PID TTY TIME CMD 9855 pts/100:00:00 bash 10363 pts/100:00:00 ps No sign of another mysql daemon running there. What to do? It used to work flawlessly. Am I hacked? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] Disclaimer http://www.shoprite.co.za/disclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
My first post!
Hello guys, I'm new with MySQL and i'm facing problem with the installation! I've Windows XP pro and when i install MySQL(mysql-4.1.2-alpha-win) it work but when i try to connect or even open mysqladmin.exe it works but not in windows mode it only work in command mode! Also when i tried to open winmysqladmin.exe it give me message: This application has failed to start because LIBEAY32.dll wasn't found, Re-install the application mayfix this problem. dose anyone know why this message?? Hope anyone answer me! Best regards Mahmoud Wasfy
ANN: Advanced Data Generator 1.1.0 released
Dear ladies and gentlemen, Upscene Productions is happy to announce a new version of the database developer tool: Advanced Data Generator (version 1.1.0) A fast test-data generator tool that comes with a library of real-life data, presets and much more. This new release consists of four versions: - Pro: ADO and ODBC connectivity - InterBase Edition - Firebird Edition - MySQL Edition More info and a 30-day trial version on www.upscene.com Pricing information available on www.upscene.com/purchase.htm#adg Recent changes: V1.1.0 --- - New: Ability to copy a (generated) value from another column in the same table - New: Masked Values can now include values from custom text files - New: Masked Values can now include sequential values with optional leading zeroes - New: Masked Values can now include random numbers - New: ability to create 1-1 relationships in addition to 1-n - New: ability to select values from lists/values random/sequential/unique - New: ability to use a VIEW as the source for referential links - Enhancement: Used SQL statement logged (useful for error tracking) - Enhancement: changed referential data retrieval to allow multi-column FKs with sequential values selection - Enhancement: several small GUI enhancements - Fixed: (Pro) delimited identifier problem with MS Access - Fixed: problem with UK street names - Fixed: Windows XP and Outlook-bar non-display of images - Fixed: several minor GUI issues With regards, Martijn Tonies Upscene Productions - Database Tools for Developers http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple locks
I need to do some operations which insert mass amounts of records which then must be sorted, and would like to use table locking. The flow I'm looking for is: LOCK TABLE A WRITE LOW_PRIO DELETE FROM TABLE A (This used to be TRUNCATE TABLE A, but that complained when I started locking) LOAD DATA INFILE ... REPLACE INTO TABLE A LOCK TABLE B WRITE *while* SELECT FROM TABLE A *process data and then* REPLACE INTO TABLE B DELETE FROM TABLE A (Also, used to be truncate) UNLOCK TABLES I don't seem to be able to lock a and then b later. I don't want to lock B from the start, because the LOAD DATA might take a while. I don't want to unlock and relock A, because other scripts may modify the data... Any suggestions, pointers, RTFMs? (I looked at 14.4.5 and links from there already) Thanks! Issac -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Errors from Extra Spaces in Spreadsheets
David: Microsoft has a converter for Works files at http://www.microsoft.com/downloads/details.aspx?FamilyID=b9e11e83-f51b-4977-b572-8c042df802c1displaylang=en I did'nt see any online documentation HTH, Martin - Original Message - From: David Blomstrom [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 07, 2004 1:33 AM Subject: OT: Errors from Extra Spaces in Spreadsheets I've been having a tough time importing comma-delimited files into my database tables. I just discovered that most of the errors are similar to this one: 060 is not a valid integer value I checked my spreadsheet - Microsoft Works - and discovered that there's a space after many of my numerals. I'm not sure how to remove these spaces. I didn't see anything in the Help files. I could do a search and replace, but that would also wipe out spaces between words - like... TrippspaceCounty I just wondered if there's a simple trick anyone knows of for getting rid of spaces like these or preventing them in the first place. Thanks. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL daemon fails after setting root password
Jesse [EMAIL PROTECTED] wrote:
anyone know why the mysql daemon fails to start after I set the root
password according to this:
http://dev.mysql.com/doc/mysql/en/Default_privileges.html
I use the command:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');
This is on SuSE 9.0 with mysql version 4.0.15
What error did you receive?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DELETE from more than one table
Hi, Is it possible to DELETE from more than one table in one command? Thanks for your help _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select double value
Good morning to all. I have a problem with a workaround but I wanted to know if others have run into this. Table DATA Column strength [double] When I select strength from DATA and the result is a non zero amount it returns correctly 3.256498 however if it is a 0 amount I get 0. The problem is that in my java.sql.ResultSet.getDouble(strength) a zero amount throws a number format exception. SO, I placed a conditional SELECT CASE WHEN strength IS NULL OR strength = 0 THEN 0 ELSE strength END This, however seems to truncate the result so that a zero return results in 0 BUT a return of 3.1236564 results in 3. Bummer. Finally I had to restructure my conditional... SELECT CASE WHEN strength IS NOT NULL AND strength != 0 THEN strength ELSE '0' END I feel that this type of data manipulation shouldn't need to be done. Is this a bug or normal for a return type of double? Thanks. Good days to all. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
