making two fields primary key ????
hi guyz i ve to alter my table n make two fields primary key (well the combination of the two to be xact) any idea how to do dis ? thnx a mill toby :S:S:S:S:S:S:S mysql query :S:S:S:S:S:S:S _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.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
Connections always max out
Ok, I've been having this problem every so often and it's happening more and more these days and I still can't figure out a solution. I know this sounds vague, but maybe you can help me make it less so... I'm running version 3.23.44 and after a while, the maximum number of connections is reached regardless of how high it's set. Running (the very handy) mytop, I see that the oldest connections are 20 minutes old (or older). Since this happens only on a live server and I need to fix it ASAP, it's hard to do quick investigation, but it seems that MySQL eventually just stops executing these old queries and over time, enough of them fill up the max connections. An Apache server running PHP is the client and I can verify (via netstat) that there are not enough connections to the server to justify the high number of connections. In normal use, the number of connections is very low, the key efficiency is high (99% or better) and the server has no trouble. But then, after several hours, the connections reach the max (and always when I'm not looking;). Does this sound familiar? The machine is not swapping either, it's as if the connections are just dead. I have no choice (it seems) but to kill the threads or restart mysqld. I know I could upgrade to a newer version, but I would prefer to understand what's happening and why first. Any ideas? Is there anything I can check to see why these connections hang out for so long? Is the lack of swapping expected, i.e., does MySQL simply refuse to try to alloc more memory than the machine physically has? Would adding RAM give the server some headroom to finish these queries? Thanks! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
padding field with zeros
Hi, Does MySQL provide for any way to pad a field? I have accounts numbers that vary from 3-5 digits, and would like them 8 digits padded by zeros (479 becomes 0479, 17234 becomes 00017234, etc). I know I could probably write a script to fix that before entering the data into the database, but I just wanted to know. Or maybe there is a way to pad it while doing the query select? Thanks, Bryan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 db as filesystem.
At 15:46 +0200 10/11/02, Alex Polite wrote: On Thu, Oct 10, 2002 at 05:56:31PM -0500, Paul DuBois wrote: At 12:58 +0200 10/10/02, Alex Polite wrote: Is there any way I could display a MySQL database as a filesystem under Linux? What does that mean? What I want is a device that actually is an interface to a db table. I mount (read only would suffice for my needs) the device on some mount point, cd into that directory and will then be able to see entries in the db table represented as files. One part of creating the device is of course the formulation of an SQL query that will decide what entries to the display, what field constitutes the file etc, etc. I can imagine a lot of uses for this. Say for instance that you want to organize digital photographs in MySQL db. (Not that I would want to do such a strange thing, just for the sake of the argument :) When you want to view the pictures you have to either write them as files and then use any picture viewer to view those files or get a picture viewer that can talk directly to MySQL. I'm very found of a picture viewer (freevo.sourceforge.net) that (at the moment) can't talk to MySQL. I want to store my pictures in MySQL and I want to be able to look at them in Freevo. So either I hack up Freevo to use MySQL or I thwart MySQL into behaving as something Freevo already knows how to access: a filesystem. alex -- Alex Polite http://plusseven.com/gpg Thanks for clarifying that. There was actually some work done on a mechanism much as you describe; you can read about it here: http://no.spam.ee/~tonu/modules.php?name=Newsfile=articlesid=3 http://no.spam.ee/~tonu/modules.php?name=Newsnew_topic=2 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: User Specific Instance Running
Anthony, Friday, October 11, 2002, 5:26:39 AM, you wrote: AWM There is non-root user implementation of MySQL-Pro 4.0.4 linux binary in AWM this users environment (RH 7.3). AWM my.cnf has been modified to include individual user, port , AWM bind-address and host specifics (and etc) unique to that user and is AWM passed as --defaults-file through mysqld_safe script. Data AWM directories are already available with this user/group's ownership. AWM All attempts to run mysql -u root file and other privledged commands This is not a privilege command :-) It means that you connect as user 'root' without a password to the database 'file'. AWM (also tried --skip-grant-tables) end up with Error 1045 Access Denied AWM ? --skip-grant-tables is an option of mysqld, not a mysql client program. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: NULL sometimes joins to NULL
jfield, Thursday, October 10, 2002, 1:33:10 AM, you wrote: jazdc After adding a key to a nullable column, null values will jazdc successfully join through to null values in other tables. jazdc This only seems to happen when the index is added after jazdc the row contains null values. This affects both MyISAM jazdc and InnoDB table types and both binary versions 3.23.42 jazdc and 4.0.4. [skip] jazdc mysql select * from foo, bar where foo.id = bar.id; jazdc +--+--+ jazdc | id | id | jazdc +--+--+ jazdc | NULL | NULL | jazdc +--+--+ jazdc 1 row in set (0.00 sec) Thanks for bug report! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: row level grants ?
Paul, Thursday, October 10, 2002, 5:59:39 PM, you wrote: PG i would like to know if row level grants are in development PG or are planned to be soon. PG i am writing a software and i need row level grants, PG so i would like to know if i'd better wait for mysql to include PG this feature or write a sort of grant feature myself PG in the software (not in mysql). In general to set grants on the some set of data you can do that using VIEWS. MySQL does not supports VIEWS yet. It's in a future plans to implement VIEWS. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: stored procedure support
Tonino, Friday, October 11, 2002, 10:05:42 AM, you wrote: T Just a short question - Does anyone know the progress of stored T procedure support in MySQL 4 Stored procedures will come in v5.0 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: How to start Mysql-max to create tables
Perumal, Saturday, October 12, 2002, 11:27:44 AM, you wrote: P Please anybody could tell me how to start mysql-max to work with DB. P What i did : start mysqld like P ./configure --prefix P =/usr/local/mysqlit starts like P Starting mysqld daemon with databases from P /usr/local/mysql-max-3.23.51-pc-linux-gnu-i686/data P 021012 12:37:18 mysqld ended P But i don't know that what command i should enter to start work with DB. safe_mysqld Look in the error logs to see why MySQL server didn't start. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 OSX 10.2
I just switched mysql from OSX 10.1.5 to 10.2.1with Marc Liyanage package but I ran with the following error : [Ordinateur-de-Pierre-Vaudrey:~] pierreva% mysql dyld: mysql Undefined symbols: /sw/lib/libreadline.4.2.dylib undefined reference to _tgetent expected to be defined in /usr/lib/libSystem.B.dylib /sw/lib/libreadline.4.2.dylib undefined reference to _tgetflag expected to be defined in /usr/lib/libSystem.B.dylib /sw/lib/libreadline.4.2.dylib undefined reference to _tgetnum expected to be defined in /usr/lib/libSystem.B.dylib /sw/lib/libreadline.4.2.dylib undefined reference to _tgetstr expected to be defined in /usr/lib/libSystem.B.dylib /sw/lib/libreadline.4.2.dylib undefined reference to _tputs expected to be defined in /usr/lib/libSystem.B.dylib /sw/lib/libreadline.4.2.dylib undefined reference to _tgoto expected to be defined in /usr/lib/libSystem.B.dylib Trace/BPT trap [Ordinateur-de-Pierre-Vaudrey:~] pierreva% Could you help me to fix it ? Pierre Vaudrey email [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQl db as filesystem.
At 12:58 Uhr +0200 10.10.2002, Alex Polite wrote: Is there any way I could display a MySQL database as a filesystem under Linux? We have created a virtual filesystem using MySQL as storage, using the LD_PRELOAD version of AVFS. The filesystem logic is written in Perl (embedded perl interpreter). It works for shell utils, proftpd, samba, and (not well tested yet) netatalk, and I'm sure WebDAV, NFS and other services could be served from it as well. It's even quite fast, the limiting factor is generally the client (Windows Explorer / Mac Finder / FTP Client / whatever). It will still take some time until I get to package the sources, though. See http://www.ethlife.ethz.ch/newcms Christian. -- Christian Jaeger Programmer System Engineer ETHLife CMS Project - www.ethlife.ethz.ch/newcms - www.ethlife.ethz.ch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 db as filesystem.
On Thu, Oct 10, 2002 at 05:56:31PM -0500, Paul DuBois wrote: At 12:58 +0200 10/10/02, Alex Polite wrote: Is there any way I could display a MySQL database as a filesystem under Linux? What does that mean? What I want is a device that actually is an interface to a db table. I mount (read only would suffice for my needs) the device on some mount point, cd into that directory and will then be able to see entries in the db table represented as files. One part of creating the device is of course the formulation of an SQL query that will decide what entries to the display, what field constitutes the file etc, etc. I can imagine a lot of uses for this. Say for instance that you want to organize digital photographs in MySQL db. (Not that I would want to do such a strange thing, just for the sake of the argument :) When you want to view the pictures you have to either write them as files and then use any picture viewer to view those files or get a picture viewer that can talk directly to MySQL. I'm very found of a picture viewer (freevo.sourceforge.net) that (at the moment) can't talk to MySQL. I want to store my pictures in MySQL and I want to be able to look at them in Freevo. So either I hack up Freevo to use MySQL or I thwart MySQL into behaving as something Freevo already knows how to access: a filesystem. alex -- Alex Polite http://plusseven.com/gpg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Alternative for NOT EXISTS
At 10:10 -0400 10/11/02, Michael T. Babcock wrote: John Ragan wrote: try corereader. it will run queries against your mysql, oracle, and ms sql server (and anything else laying around). you can switch between your servers with a click. Am I the only one getting tired of these non-SQL related advertising responses? Feel free to put this in your tagline or something, but please actually bother to answer the question or be otherwise useful to the poster instead of _only_ advertising a product in every post. I agree. The product is free, which is a plus, but since these numerous responses don't answer the questions posed, it'd be more appropriate for John to reply onto to the poster rather than to the list. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. 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
Re: MySQl db as filesystem.
Pretty interesting stuff: http://www.osnews.com/story.php?news_id=836 http://newsforge.com/article.pl?sid=02/03/22/204220tid=9 Anthony Anthony W. Marino Pres./CTO, AWM Objects David Garamond wrote: Jan Steinman wrote: Today's modern operating systems really stopped evolving in the 80's. Many ideas like database filesystems never really got a chance to show their utility. isn't the next windows operating system (longhorn) supposed to have a new filesystem that's based on SQL Server? i also read that Microsoft had long contemplated about doing this, even before Windows XP/2000... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
innodb not using correct index
Is there a way to find out what index an sql query is using? I know you can use explain, but those are just possible indexes that the query might use. I'm trying to find out exacly what execution path it is taking. Something similar to Oracle's set autotrace on; MySql version - MySQL-Max-3.23.52-1 OS version - RedHat 2.4.18-SGI_XFS_1.1enterprise Here is the problem I'm having. I've got a simple query which is taking about 1 1/2 minutes to run. select several_fields from xray where create_date '2002-10-03' and cust_id = 'TEST1'; I have the following indexes. X_CREATE_DATE(create_date, cust_id). X_CUST_ID(cust_id, download_file). If I run select several_fields from xray where create_date '2002-10-03'; It takes less the 10 seconds. If I run select several_fields from xray where cust_id = 'TEST1'; It also takes less than 10 seconds. If I run the query on my Oracle test server (sparc 20, dual 60mhz, 160mb ram, old ass raid with 5400rpm scsi drives), it takes about 10 seconds to return the 374 rows. The mysql server is a PIII 600mhz, 500mb ram, with 2 10K rpm lvd scsi drives attached to an Ultra 160 scsi card and is much faster when doing single where clauses than the oracle test server. To satisfy my curiosty, I wrote a perl script that selected all the cust_id where create_date '2002-10-03' and then checked the cust_id against TEST1' and it only took about 15 seconds. I origionally only had create_date in the X_CREATE_DATE index, but I figured adding cust_id along with it would make it faster but it didn't. I'm open to any suggestions or comments. This table 46 columns * 2.2million rows and has 13 indexes (full table scans bring the db to it's knees). It's still a test database so I can try most anything to speed this up. Thanks! sql, query -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: LOAD DATA INFILE problem
Tim, Saturday, October 12, 2002, 1:21:59 AM, you wrote: TJ I am attempting to delete a table from a database, TJ then recreate it and load data into the table. TJ The drop and create commands are working properly, TJ here's a screen dump: TJ mysql DROP TABLE IF EXISTS testthis; TJ Query OK, 0 rows affected (0.16 sec) TJ mysql CREATE TABLE testthis(ID INT DEFAULT '0',Reviews CHAR (50),Date DATE,Bookmark CHAR (50), TJ - Title CHAR (90),Web_Page CHAR (50)); TJ Query OK, 0 rows affected (0.16 sec) TJ mysql LOAD DATA INFILE /home/http/run/isak/wmj-new/uploaded/testthis.txt INTO TABLE testthis; TJ ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) TJ The same error message is generated if I give the filename only, logging TJ in from the working directory of the file. TJ 1)Any ideas of what I may be doing wrong here? TJ 2)Also, can anyone tell me where I can find a list of MySQL error codes? TJ Understanding more of what ERROR 1045 is about might be helpful User must have FILE privilege to use LOAD DATA INFILE. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 use comma instead dot in numeric values?
I had the same problem. No, in MySQL you can't. But you can use number_format() function of PHP. Check PHP Manual for How-to Nikos --- Putte Koivisto [EMAIL PROTECTED] : Is there a simple solution to define MySQL to use comma instead of dot in numeric fields? Sincerely, Putte koivisto - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Do You Yahoo!? @yahoo.grhttp://www.otenet.gr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 query - join two tables - order by goes crazy
Group, I have the following query: select phones.*, ops.plid, ops.box, ops.mac from phones, ops where (ops.box = 'Mcds') or (ops.box = 'Mn3300') and (phones.suffix1 = ops.phone) order by ops.mac When I change the order by to a field in the phones table - sorts just fine and produces results but in this case I am sorting by one of the fields in the other table (ops). massive bunch of disk activity and mysqladmin shows that the process is writing stuff to disk then sorting then producing the results. Am I doing this query incorrectly? BTW - I am Win32 mysql version. Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL 3.23.49
We found the same thing, after installing the glibc auto-rpm from Redhat. The updated glibc packages from Red Hat will whack your /etc/nsswitch.conf file. That is, it will mess with the order of how things are searched for. We noticed this with mysql not being able resolve remote server IPs. Removing any reference to 'nisplus' in nsswitch.conf helped this problem on RH7.1 (glib2.2.4) systems. Another, related problem that only appears on RH7.3 (glibc2.2.5) was remedied by putting 'dns' at the front of any list of methods that require reverse-dns lookups (i.e. hosts:dns files nis). We downloaded the MySql pre-built 3.23.52, and that solved the problem. J Ross Nicoll wrote: I seem to have found a bug in MySQL 3.23.49 (untested in later versions). It appears that if the server cannot resolve the hostname of an incoming TCP connection, it crashes. I've been testing this under a RedHat 7.2/7.3 hybrid, and only came across the problem because I've managed to somehow stop DNS from working correctly! Unfortunately, I don't yet know exactly what's wrong with DNS. Putting the hostname of the incoming IP address into the /etc/hosts file has solved this for us, but I thought you might want to know. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Mysqld DNS lookup causes crash
Description: Server version 3.23.49-log Protocol version10 OS Linux atlas 2.4.18-5smp #1 Mysqld two days ago began to crash on accepting non-local mysql connections. After a day of database testing and debug researching I used the --skip-name-resolve in the startup script because we noted that auth wasn't even having time to get checked on the connection. We needed to start looking before auth. This new cmd option allows the server to run. No system changes have occured, no hardware crashes. It is all very bizare to us. How-To-Repeat: To reintroduce the problem take out --skip-name-resolve and connect remotely. Fix: Add --skip-name-resolve to the startup script. Submitter-Id: submitter ID Originator:Andy Ciordia Organization: Planned Giving Design Center MySQL support: none Synopsis: Mysqld crashing on DNS lookup Severity: serious Priority: medium Category: mysql Class: Release: mysql-3.23.49 (Source distribution) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.49, for redhat-linux-gnu on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 6 min 6 sec Threads: 1 Questions: 86 Slow queries: 0 Opens: 41 Flush tables: 1 Open tables: 30 Queries per second avg: 0.235 Environment: System: Linux atlas 2.4.18-5smp #1 SMP Mon Jun 10 15:19:40 EDT 2002 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.3 2.96-112) Compilation info: CC='gcc' CFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' CXX='gcc' CXXFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Oct 7 18:29 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1260480 Sep 9 10:16 /lib/libc-2.2.5.so -rw-r--r--1 root root 2312410 Sep 9 10:02 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 9 09:56 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Aug 26 12:55 /usr/lib/libc-client.a - c-client.a Configure command: ./configure i386-redhat-linux --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --includedir=/usr/include --libdir=/usr/lib --libexecdir=/usr/libexec --localstatedir=/var --sharedstatedir=/usr/com --mandir=/usr/share/man --infodir=/usr/share/info --without-debug --without-readline --enable-shared --with-extra-charsets=complex --with-bench --localstatedir=/var/lib/mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --with-mysqld-user=mysql --with-extra-charsets=all --with-innodb --with-berkeley-db --enable-large-files=yes --enable-largefile=yes --with-thread-safe-client --enable-assembler - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query going crazy
Group, I have the following query: select phones.*, ops.plid, ops.box, ops.mac from phones, ops where (ops.box = 'Mcds') or (ops.box = 'Mn3300') and (phones.suffix1 = ops.phone) order by ops.mac When I change the order by to a field in the phones table - sorts just fine and produces results but in this case I am sorting by one of the fields in the other table (ops). massive bunch of disk activity and mysqladmin shows that the process is writing stuff to disk then sorting then producing the results. Am I doing this query incorrectly? BTW - I am Win32 mysql version. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Error
At 13:15 +0300 10/11/02, Victoria Reznichenko wrote: Abhi, Thursday, October 10, 2002, 8:30:43 PM, you wrote: A I am not able to connect mysql server after executing this command A ./safe_mysqld -O key_buffer=64M -O table_cache=256 -O A sort_buffer=4M -O read_buffer_size=1M The probable problem here is that read_buffer_size is called record_buffer prior to MySQL 4.0.3. A [repository@shoggoth bin]$ ./mysql -u root A ERROR 2002: Can't connect to local MySQL server through socket A '/tmp/mysql.sock' (2) [skip] A but my process is running. I have tryed to restart also but unable to A restart mysql server successifully. A mysql show processlist; A +---+--+---+-+-+--+- A -+--+ A | Id| User | Host | db | Command | Time | A State A | Info | A +---+--+---+-+-+--+- A -+--+ A | 19509 | root | localhost | mysql | Sleep | 481 A | A | NULL | A | 19532 | root | localhost | DS_data | Query | 0| A NULL A | show processlist | A | 19547 | root | localhost | DS_data | Query | 39 | Copying to tmp A table on A disk | select distinct name_server1 from tbl_raw_zone_file limit 10 | A +---+--+---+-+-+--+- A -+--+ A The error (2002) Can't connect to ... normally means that there isn't a A MySQL server running on the system or A that you are using a wrong socket file or TCP/IP port when trying to A connect to the mysqld server. A Error Log A - A 021010 10:30:41 mysqld started A No variable match for: -O 'read_buffer_size=1M' A /usr/local/mysql/libexec/mysqld Ver 3.23.49 for pc-linux-gnu on i686 A Use '--help' or '--no-defaults --help' for a list of available o A I am using mysql version 3.23 A mysql select version(); A +-+ A | version() | A +-+ A | 3.23.49-log | A +-+ A 1 row in set (0.00 sec) A ptions A 021010 10:30:41 mysqld ended ^ But error log shows that MySQL didn't start. Do you have another MySQL server running? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQl db as filesystem.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Actually, I remember reading about a mysqlfs system, like that, cd to a table, ls for records.. rm to remove and such. that was over a year ago at least.. check sourceforge or google, I bet they'd be able to tell more. Archives might too. Jayce^ On Thursday 10 October 2002 10:32 pm, Joel Rees wrote: At 12:58 +0200 10/10/02, Alex Polite wrote: Is there any way I could display a MySQL database as a filesystem under Linux? To which, on Thu, 10 Oct 2002 at 17:56:31 -0500, Paul DuBois asked What does that mean? Should we guess that he wants to be able to log into a database with csh, run ls and get a list of tables, run cat on a table and get a tab-delimited listing of the contents of the table? On the surface it didn't seem like such an unreasonable question, ... - -- - --Jayce^ -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE9pxhyA10/0O8cAHgRAp7kAJ972XyI6+ys+X3rzc13/2mprw2HTwCeOy2m iuG//UzD37flqBKMVwvcDrg= =bT50 -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 make individual batch SQL silent
rick wrote: I know how to make a whole batch SQL script silent (-s option of mysql), but is there a way within a script to make one or more individual statements silent? In Oracle PL/SQL, it can be done using echo on/off and termout on/off. Just for testing, I created a script noecho.sh: #!/bin/sh exec cat $1 /dev/null Then I did pager noecho.sh and it stopped responding ... but it still says 100 rows in set ... and so forth. YMMV. ... oh, and SQL. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. 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
Re: basic question
At 11:50 -0400 10/11/02, Edward Peloke wrote: sql query when I am defining a table, how do I set the default of a datetime column to the current time? I tried setting the default to now() but it just fills the column with 00:00 etc.. You don't. Default values must be constants. The TIMESTAMP column type might be useful to you for this purpose. But read about it in the manual to be sure you're aware of all its properties. Thanks, Eddie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LOAD DATA INFILE problem
Hello All: I am attempting to delete a table from a database, then recreate it and load data into the table. The drop and create commands are working properly, here's a screen dump: mysql DROP TABLE IF EXISTS testthis; Query OK, 0 rows affected (0.16 sec) mysql CREATE TABLE testthis(ID INT DEFAULT '0',Reviews CHAR (50),Date DATE,Bookmark CHAR (50), - Title CHAR (90),Web_Page CHAR (50)); Query OK, 0 rows affected (0.16 sec) mysql LOAD DATA INFILE /home/http/run/isak/wmj-new/uploaded/testthis.txt INTO TABLE testthis; ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) The same error message is generated if I give the filename only, logging in from the working directory of the file. 1)Any ideas of what I may be doing wrong here? 2)Also, can anyone tell me where I can find a list of MySQL error codes? Understanding more of what ERROR 1045 is about might be helpful this is server version: 3.23.51 TIA -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com http://www.johnsons-web.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: copy one database to another
At 11:01 +0530 10/11/02, Insanely Great wrote: Greetings... I dont think that there is any SQL statement to do that. As of changing the name, You can just go to the data directory of MySQL and change the name of the directory of the database. MySQL stores each database as a directoyr so changing the name of the directory will change the name of the database. Make sure to FLUSH TABLES first. But it won't work if you have InnoDB tables, and isn't supported if you have BDB tables. I have tried this with MyISAM and ISAM tables, not sure of other table types. See above. Best just to do what others have suggested: use mysqldump to dump the database, create the new database, and reload the dump file into it. Rgds Insane SQLyog - The Definative GUI for MySQL http://www.webyog.com/sqlyog - Original Message - From: Scott Pippin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 11, 2002 4:25 AM Subject: copy one database to another I have one database(test) i would like to copy over to (test1). Ehat is the syntax to do this? Is there any to just change the name of the database? Thanks in advance (query, mysql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Alternative for NOT EXISTS
ok. At 10:10 -0400 10/11/02, Michael T. Babcock wrote: John Ragan wrote: try corereader. it will run queries against your mysql, oracle, and ms sql server (and anything else laying around). you can switch between your servers with a click. Am I the only one getting tired of these non-SQL related advertising responses? Feel free to put this in your tagline or something, but please actually bother to answer the question or be otherwise useful to the poster instead of _only_ advertising a product in every post. I agree. The product is free, which is a plus, but since these numerous responses don't answer the questions posed, it'd be more appropriate for John to reply onto to the poster rather than to the list. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.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 make individual batch SQL silent
At 6:58 -0500 10/11/02, rick wrote: I know how to make a whole batch SQL script silent (-s option of mysql), but is there a way within a script to make one or more individual statements silent? In Oracle PL/SQL, it can be done using echo on/off and termout on/off. Not really, though maybe this ugly hack will help: mysql \P /dev/null mysql \n The first sends query output to /dev/null, the second restores normal output. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Lost connection to MySQL server during query
Hi there, This is an error message from MySQL when I try to connect to my remote MySQL server. Does anyone know what this message mean. I'm completely puzzled. I used to make the same connection with the exactly the same command routinely get connected easily. But all in a sudden, I cannot make the connection at all. I looked on the localhost. I can login on the localhost just fine. I simply can login from another machine. I did not upgrade the MySQL in the mean time. What could be the reason?? Please help. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to distribute a MySQL database?
Hi folks, I am using the MySQL C++ API to put together a football management game. I wont bore anyone with the implementation details, basically I would like to know how to distribute the games database so that an end-user does not need to have MySQL installed to run it. I am running MySQL 3.23.44-nt on WinXP if that is any help. -Tim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 db as filesystem.
There has been such a project for quite a while. Take a look at http://no.spam.ee/~tonu/ and find the link about SQLFS. j- k- On Thursday 10 October 2002 02:58, Alex Polite wrote: Is there any way I could display a MySQL database as a filesystem under Linux? alex -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
query going crazy.
Group, I have the following query: select phones.*, ops.plid, ops.box, ops.mac from phones, ops where (ops.box = 'Mcds') or (ops.box = 'Mn3300') and (phones.suffix1 = ops.phone) order by ops.mac When I change the order by to a field in the phones table - sorts just fine and produces results but in this case I am sorting by one of the fields in the other table (ops). massive bunch of disk activity and mysqladmin shows that the process is writing stuff to disk then sorting then producing the results. Am I doing this query incorrectly? BTW - I am Win32 mysql version. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 embedded mysql library apps accessing the same database
I was wondering, what happens if two programs use the embedded mysql library and try to access the same database? Or, what happens if you run the mysql database server on the machine and use the embedded mysql library to access a database? how does the mysql lib require the application writer to handle multiple independent access? -thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Finding missing ID
Chuck, I have database that has grown in size, I want to see what ID files have been deleted. Is there a way or do I have to print out the list and find them myself? Assuming by files you mean rows, and assuming a table named t1, a column in it named id, and you having done something like ... SELECT @idmax := MAX( id ) FROM t1; then this gives you missing numbers: SELECT t1.id FROM t1 LEFT JOIN t1 AS t2 ON t1.id = t2.id-1 WHERE t2.id IS NULL AND t1.id @idmax; Once MySQL gives us subqueries in 4.1 (December?), it'll be easier ... SELECT id AS id1 from t1 WHERE id idmax AND NOT EXISTS (SELECT id FROM t1 WHERE id = id1 - 1); HTH PB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 3.23.47 Replication - Failed Read
Dear Sir/Madam, I have tried the config below, and I successfully see the replicated record in client side but only ONCE. After I have seen the replication works. I continued with my normal transactional work including altering table structure. But later when I checked the replication status. Well, it has been in the state of frozen for some time described below. Now the server position keep in position 69 (show master status) and client side keep staying in position 29 (show slave status). Previously the server position keep staying in 73 but after I done a series of reset master, reset slave and flush logs in server side, it turned to 69 and stay there. The client side is showing the message Waiting to reconnect after failed read (read using show processlist) Here's the series of same error log generated in client-side (mysql.err) 020816 15:31:21 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 29 020816 15:31:21 Slave: received 0 length packet from server, apparent master shutdown: (0) 020816 15:31:21 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 29 020816 15:31:48 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 29 020816 15:31:48 Slave: received 0 length packet from server, apparent master shutdown: (0) 020816 15:32:49 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 29 ... I have tried turning off both servers, overwriting of server database over to client's database folder. Deleting off the bin logs and restarting server-side mysql and client-side mysql but it still stay in the position I described above Well, I hope I could get something from MySQL soon. Thank you! The following are my servers settings Both servers are using version 3.23.47 Server (win2000 professional, IP 192.168.100.30) side ini settings [mysqld] basedir=C:/MYSQL #bind-address=192.168.100.30 datadir=C:/MYSQL/data #language=C:/MYSQL/share/your language directory #slow query log#= #tmpdir#= #port=3306 #set-variable=key_buffer=16M server-id=3 binlog-do-db=DISYS_DB log-bin=C:/MYSQL/MyLog/mykhorep.log Client (win98, IP 192.168.100.4) side ini settings [mysqld] basedir=D:/MYSQL #bind-address=192.168.100.4 datadir=D:/MYSQL/data #language=D:/MYSQL/share/your language directory #slow query log#= #tmpdir#= #port=3306 #set-variable=key_buffer=16M master-host=192.168.100.30 master-user=repl master-password=password master-port=3306 server-id=3 master-connect-retry=60 replicate-do-db=DISYS_DB Regards, Khor --- [This E-mail scanned for viruses by Declude antiVirus] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: padding field with zeros
At 10:11 -0700 10/11/02, Bryan Koschmann - GKT wrote: Hi, Does MySQL provide for any way to pad a field? I have accounts numbers that vary from 3-5 digits, and would like them 8 digits padded by zeros (479 becomes 0479, 17234 becomes 00017234, etc). I know I could probably write a script to fix that before entering the data into the database, but I just wanted to know. Or maybe there is a way to pad it while doing the query select? INT(8) ZEROFILL, perhaps? Thanks, Bryan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: padding field with zeros
See the ZEROFILL column attribute: http://www.mysql.com/doc/en/Column_types.html http://www.mysql.com/doc/en/Numeric_types.html If necessary, you can use an ALTER TABLE command to add that attribute to the appropriate columns. - steve At 10:11 AM -0700 10/11/02, Bryan Koschmann - GKT wrote: Hi, Does MySQL provide for any way to pad a field? I have accounts numbers that vary from 3-5 digits, and would like them 8 digits padded by zeros (479 becomes 0479, 17234 becomes 00017234, etc). I know I could probably write a script to fix that before entering the data into the database, but I just wanted to know. Or maybe there is a way to pad it while doing the query select? Thanks, Bryan -- ++ | Steve Edberg [EMAIL PROTECTED] | | University of California, Davis (530)754-9127 | | Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ | ++ | The end to politics as usual: | | The Monster Raving Loony Party (http://www.omrlp.com/) | ++ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to start Mysql-max to create tables
Dear sir, Please anybody could tell me how to start mysql-max to work with DB. What i did : start mysqld like ./configure --prefix =/usr/local/mysqlit starts like Starting mysqld daemon with databases from /usr/local/mysql-max-3.23.51-pc-linux-gnu-i686/data 021012 12:37:18 mysqld ended But i don't know that what command i should enter to start work with DB. Perumal. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Weird error when adding a fulltext index
Description: When I create a fulltext index I get: ERROR 1005: Can't create table './inventory/#sql-4024_116c.frm' (errno: 140) How-To-Repeat: alter table inventory.item add fulltext(storeId, catId, itemNo, title, type, publisher, condition, color, subCategory, description, location, edition); describe inventory.item; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | storeId | varchar(64) | | MUL | || | catId | varchar(64) | | | || | subCategory | varchar(128) | YES | | NULL || | status | varchar(64) | | MUL | forsale || | type| varchar(64) | YES | | NULL || | description | text | | | || | itemNo | varchar(64) | | | || | size| varchar(128) | YES | | NULL || | qty | int(11) | YES | | 1 || | imageFile | varchar(255) | YES | | NULL || | title | varchar(128) | YES | | NULL || | subTitle| text | YES | | NULL || | author | varchar(128) | YES | | NULL || | pages | varchar(32) | YES | | NULL || | isbn| varchar(128) | YES | | NULL || | publisher | varchar(128) | YES | | NULL || | year| varchar(32) | YES | | NULL || | edition | int(5) | YES | | NULL || | enteredStamp| datetime | | MUL | -00-00 00:00:00 || | updatedStamp| datetime | | MUL | -00-00 00:00:00 || | condition | varchar(32) | YES | | NULL || | isNew | tinyint(4) | YES | | NULL || | weight | varchar(32) | YES | | NULL || | color | varchar(64) | YES | | NULL || | seqNo | int(4) | YES | | NULL || | price | decimal(7,2) | YES | | NULL || | unitMeasure | varchar(50) | YES | | NULL || | deletedStamp| datetime | | MUL | -00-00 00:00:00 || | location| varchar(128) | YES | | NULL || | auctionMin | decimal(7,2) | YES | | NULL || | auctionReserve | decimal(7,2) | YES | | NULL || | purchaseCost| decimal(7,2) | YES | | NULL || | purchaseDate| date | YES | | NULL || | itemKey | int(8) unsigned | | PRI | NULL | auto_increment | | categoryKey | int(8) unsigned | YES | MUL | NULL || | dealerPrice | decimal(7,2) | YES | | NULL || | retailPrice | decimal(7,2) | YES | | NULL || | minQty | int(11) unsigned | | | 1 || | qtyPerUnit | int(7) | YES | | NULL || | audited | tinyint(1) | YES | | 0 || | upcCode | varchar(12) | YES | | NULL || | accountKey | int(8) unsigned | YES | | NULL || | countryOfOrigin | char(2) | YES | | NULL || | serialized | tinyint(4) | YES | | NULL || | reorderPoint| int(8) | YES | | NULL || | makeVBuy| tinyint(1) | YES | | NULL || +-+--+--+-+-++ mysql show keys from inventory.item; +---+++--+--+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--+--+---+-+--++--++-+ | item | 0 | PRIMARY|1 | itemKey | A | 1715210 | NULL | NULL | | BTREE | | | item | 0 | oldItemKey |1 |
re: query going crazy.
Norris, Saturday, October 12, 2002, 2:11:36 AM, you wrote: NJ I have the following query: NJ select phones.*, ops.plid, ops.box, ops.mac NJ from phones, ops where NJ (ops.box = 'Mcds') or (ops.box = 'Mn3300') and NJ (phones.suffix1 = ops.phone) order by ops.mac NJ When I change the order by to a field in the phones table - sorts just fine NJ and produces results but in this case I am sorting by one of the fields in NJ the other table (ops). NJ massive bunch of disk activity and mysqladmin shows that the process is NJ writing stuff to disk then sorting then producing the results. What is the output of EXPLAIN SELECT? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Lost connection to MySQL server during query
Hello After a reboot, the database server running Redhat 7.2 and mySQL 3.23.41-1 started acting funny. When I try to telnet to port 3306 of the server, nothing happens and when i check in the log file for mysql, i get something like: Number of processes running now: 1 mysqld process hanging, pid 11437 - killed 021012 01:24:09 mysqld restarted /usr/libexec/mysqld: ready for connections (It looks like mysql crash when i connect) At the start, i had no clue what was going on, i checked all the tables with myisamchk. No errors found. I had a secound server, with Redhat 7.3 and mySQL. I don't know how smart this is, but i deletet /var/lib/mysql/* and replaced the files with the ones from the other server.(Containing the database data). Now, the same problem was on that server. (As on the other, with the error message: Lost connection to server during query ) I removed the /var/lib/mysql/* files again, and replaced them with the old backup, and restarted mySQL. Still the same problem. (Lost connection..) I now deletet all the mysql rpm's (and deletet /var/log/mysql/*) and reinstalled them. Still the same problem. Im not a guru or anything, but for me it looks like it act like a virus. (As i can't find any solution). The thing is: I can use the mysql client and login to localhost (mysql -u root -p -h localhost) but if i use the server ip, i get the lost connection error. Don't know if this is a bug, or what it is, but if you could help, i would be greatfull. Best regards. Øystein - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Importing Data from Text File, HOW IGNORE SOME TEXT COLUMNS???
Hello, #If I have a file t.txt 111 222 333 ddd 444 ddd 111 222 333 ddd 444 ddd 111 222 333 ddd 444 ddd # A table CREATE TABLE userdata ( a1 varchar(128) NOT NULL default '', a2 varchar(128) NOT NULL default '', a3 varchar(128) NOT NULL default '', a4 varchar(128) NOT NULL default '', KEY a1 (a1), KEY a2 (a2), KEY a3 (a3), KEY a4 (a4) ) TYPE=MyISAM; # How insert into table - text columns with indexes: (1, 2, 3, 5) ?? # How ignore text columns with indexes (4, 6) ??? mysql LOAD DATA LOCAL INFILE 't.txt' INTO TABLE userdata FIELDS mysql TERMINATED BY ',' ENCLOSED BY '' (a1, a2, a3, a4); # If need to have this rezult: mysql select * from userdata; +-+-+-+-+ | a1 | a2 | a3 | a4 | +-+-+-+-+ | 111 | 222 | 333 | 444 | | 111 | 222 | 333 | 444 | | 111 | 222 | 333 | 444 | +-+-+-+-+ Thanks Very much. Alvydas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 db as filesystem.
Here's something: http://no.spam.ee/~tonu/modules.php?name=Newsnew_topic=2 Anthony Jayce^ wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Actually, I remember reading about a mysqlfs system, like that, cd to a table, ls for records.. rm to remove and such. that was over a year ago at least.. check sourceforge or google, I bet they'd be able to tell more. Archives might too. Jayce^ On Thursday 10 October 2002 10:32 pm, Joel Rees wrote: At 12:58 +0200 10/10/02, Alex Polite wrote: Is there any way I could display a MySQL database as a filesystem under Linux? To which, on Thu, 10 Oct 2002 at 17:56:31 -0500, Paul DuBois asked What does that mean? Should we guess that he wants to be able to log into a database with csh, run ls and get a list of tables, run cat on a table and get a tab-delimited listing of the contents of the table? On the surface it didn't seem like such an unreasonable question, ... - -- - --Jayce^ -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE9pxhyA10/0O8cAHgRAp7kAJ972XyI6+ys+X3rzc13/2mprw2HTwCeOy2m iuG//UzD37flqBKMVwvcDrg= =bT50 -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
High load with cleaning up status
Recently our DB, which is running MySQL 3.23.52, started experiencing intermittant load spikes, of the magnitude of 200+ load averages. Normally, our high spike is about 0.90 load. When this starts happening, we notice a lot of processes cleaning up within the database. Can someone clue me in as to what cleaning up means, and what the possible cause of this is? Thanks, Mike Zimmerman - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA INFILE problem
On Friday, Oct 11, 2002, at 15:21 America/Phoenix, Tim Johnson wrote: Hello All: I am attempting to delete a table from a database, then recreate it and load data into the table. The drop and create commands are working properly, here's a screen dump: mysql DROP TABLE IF EXISTS testthis; Query OK, 0 rows affected (0.16 sec) mysql CREATE TABLE testthis(ID INT DEFAULT '0',Reviews CHAR (50),Date DATE,Bookmark CHAR (50), - Title CHAR (90),Web_Page CHAR (50)); Query OK, 0 rows affected (0.16 sec) mysql LOAD DATA INFILE /home/http/run/isak/wmj-new/uploaded/testthis.txt INTO TABLE testthis; ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) The same error message is generated if I give the filename only, logging in from the working directory of the file. 1)Any ideas of what I may be doing wrong here? 2)Also, can anyone tell me where I can find a list of MySQL error codes? Understanding more of what ERROR 1045 is about might be helpful In Unix systems the mysql user needs file privileges to file to be loaded. It's probably simplest to have the file located on a local disk (not network shared) with privileges for all to read. -- Clayburn W. Juniel, III Phone: (602) 326-7707 Email: [EMAIL PROTECTED] http://EffectiveSoftwareSolutions.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: Mysqld DNS lookup causes crash
we had the same problem. We had mysql and BIND running on single server. Upgrading BIND helped to solve this problem. .:: Andrius Armonas ::. .:: [EMAIL PROTECTED] ::. .:: http://baubas.andrius.org ::. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, October 12, 2002 12:52 AM Subject: Mysqld DNS lookup causes crash Description: Server version 3.23.49-log Protocol version10 OS Linux atlas 2.4.18-5smp #1 Mysqld two days ago began to crash on accepting non-local mysql connections. After a day of database testing and debug researching I used the --skip-name-resolve in the startup script because we noted that auth wasn't even having time to get checked on the connection. We needed to start looking before auth. This new cmd option allows the server to run. No system changes have occured, no hardware crashes. It is all very bizare to us. How-To-Repeat: To reintroduce the problem take out --skip-name-resolve and connect remotely. Fix: Add --skip-name-resolve to the startup script. Submitter-Id: submitter ID Originator: Andy Ciordia Organization: Planned Giving Design Center MySQL support: none Synopsis: Mysqld crashing on DNS lookup Severity: serious Priority: medium Category: mysql Class: Release: mysql-3.23.49 (Source distribution) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.49, for redhat-linux-gnu on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 6 min 6 sec Threads: 1 Questions: 86 Slow queries: 0 Opens: 41 Flush tables: 1 Open tables: 30 Queries per second avg: 0.235 Environment: System: Linux atlas 2.4.18-5smp #1 SMP Mon Jun 10 15:19:40 EDT 2002 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.3 2.96-112) Compilation info: CC='gcc' CFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_L ARGEFILE_SOURCE' CXX='gcc' CXXFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D _LARGEFILE_SOURCE -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Oct 7 18:29 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1260480 Sep 9 10:16 /lib/libc-2.2.5.so -rw-r--r--1 root root 2312410 Sep 9 10:02 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 9 09:56 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Aug 26 12:55 /usr/lib/libc-client.a - c-client.a Configure command: ./configure i386-redhat-linux --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbind ir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --includedir=/usr/includ e --libdir=/usr/lib --libexecdir=/usr/libexec --localstatedir=/var --shareds tatedir=/usr/com --mandir=/usr/share/man --infodir=/usr/share/info --without -debug --without-readline --enable-shared --with-extra-charsets=complex --wi th-bench --localstatedir=/var/lib/mysql --with-unix-socket-path=/var/lib/mys ql/mysql.sock --with-mysqld-user=mysql --with-extra-charsets=all --with-inno db --with-berkeley-db --enable-large-files=yes --enable-largefile=yes --with -thread-safe-client --enable-assembler - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 OSX 10.2
On Saturday, Oct 12, 2002, at 04:24 America/Phoenix, Pierre Vaudrey wrote: I just switched mysql from OSX 10.1.5 to 10.2.1with Marc Liyanage package but I ran with the following error : [Ordinateur-de-Pierre-Vaudrey:~] pierreva% mysql dyld: mysql Undefined symbols: /sw/lib/libreadline.4.2.dylib undefined reference to _tgetent expected to be defined in /usr/lib/libSystem.B.dylib /sw/lib/libreadline.4.2.dylib undefined reference to _tgetflag expected to be defined in /usr/lib/libSystem.B.dylib /sw/lib/libreadline.4.2.dylib undefined reference to _tgetnum expected to be defined in /usr/lib/libSystem.B.dylib /sw/lib/libreadline.4.2.dylib undefined reference to _tgetstr expected to be defined in /usr/lib/libSystem.B.dylib /sw/lib/libreadline.4.2.dylib undefined reference to _tputs expected to be defined in /usr/lib/libSystem.B.dylib /sw/lib/libreadline.4.2.dylib undefined reference to _tgoto expected to be defined in /usr/lib/libSystem.B.dylib Trace/BPT trap [Ordinateur-de-Pierre-Vaudrey:~] pierreva% Could you help me to fix it ? There is a separate package for 10.2 from Marc Liyanage. If that doesn't work, you can get a source distribution and compile it your self. When I did that I got a few errors during make and make install. I set the -i flag to ignore errors and it compiled and ran OK. -- Clayburn W. Juniel, III Phone: (602) 326-7707 Email: [EMAIL PROTECTED] http://EffectiveSoftwareSolutions.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
Finding missing ID
Hi, I have database that has grown in size, I want to see what ID files have been deleted. Is there a way or do I have to print out the list and find them myself? Is there a sql or query statement that will let me find them. Chuck Payne - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 db as filesystem.
From: Paul DuBois [EMAIL PROTECTED] At 12:58 +0200 10/10/02, Alex Polite wrote: Is there any way I could display a MySQL database as a filesystem under Linux? What does that mean? There's a long (though obscure) history of using database-like engines as filesystems. There are considerable advantages, such as user-defined file metadata, journalling, simple to implement automatic version control, super-fast searching, etc, that would make this attractive to many folks. I believe the Pick operating system from the 70's had a database filesystem, for example. It was popular among business types. As I recall, it also had a BASIC command interpreter as its primary way of interacting with the system. Today's modern operating systems really stopped evolving in the 80's. Many ideas like database filesystems never really got a chance to show their utility. I agree that it would be interesting to slide MySQL under a filesystem, but it sounds like a lot of work! SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL -- : Jan Steinman -- nature photography: http://www.Bytesmiths.com : Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services : Join the forums at http://www.Bytesmiths.com/wiki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Lost connection to MySQL server during query
Hi there, Lost connection to MySQL server during query This is an error message from MySQL when I try to connect to my remote MySQL server. Does anyone know what this message mean. I'm completely puzzled. I used to make the same connection with the exactly the same command routinely get connected easily (on RH7.3). But all in a sudden, I cannot make the connection at all. I looked on the localhost. I can login on the localhost just fine. I simply can not login from another machine. I did not upgrade the MySQL in the mean time, or make any structural changes to the DBs. What could be the reason?? Please help. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php