Re: myisamchk vs. mysqlcheck
Mark, Is the only reason for this the fact that I have mysql running? Are mysqlcheck and myisamchk doing the exact same thing. Or are there scenarios in which one reports errors and the other doesn't. mysqlcheck and myisamchk are doing the same things _basically_. mysqlcheck does all the operations you can perform on the SQL command line (OPTIMIZE TABLE etc.), myisamchk can do a bit more. mysqlcheck will work on BDB and InnoDB tables for a number of operations, myisamchk will only work on MyISAM tables. Does mysqlcheck lock tables while checking? If so, does it do full locks or is read-access possible during the check is performed? And now (most important in this context): mysqlcheck will take care to flush and lock the tables it performs operations on, myisamchk won't. So if you're not taking down the server, you will have to do the following (probably with the help of some script): LOCK TABLE tbl1; FLUSH TABLES; --- Don't forget about this one! ... perform the myisamchk operation(s) ... UNLOCK TABLES tbl1; LOCK TABLE tbl2; etc. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: Cannot Connect
RBE, 4) How exactly are you tring to connect (mysql -hlocalhost -uuser -psecret)? One of the problems is that I was not only not able to enter a password, but cannot seem to get one entered. I get error 2002 with that or almost any other entry. So far, following the suggestions given me in the manual doesn't seem to fix anything. Sorry, but this doesn't answer my question. You said you used MySQL Monitor (the command line tool) when trying to connect to the server. When you started MySQL Monitor, how did you do that? Did you just double-click mysql (the MySQL Monitor) in some graphical file manager tool, or did you cd to the directory where mysql is sitting, and then type mysql? To make this more clear. Under Windows, I do the following on the command line: C:\ cd mysql\bin C:\mysql\bin mysql -usuperuser -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.10-gamma-max-nt-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql Now I'm connected to the server, and I can issue SQL statements. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Need Help With MySQL Query
hello, I've performed searches on this site and php.net to try and figure out why this is occuring. I can't find any instance in my searches that helped me. So, I'm posting my very first question to this list Here's my table ($usertableDaily): vanNumber|grossPay - | 1000 | 500 |100 |100 Here's my query: SELECT SUM(grossPay) FROM $usertableDaily WHERE vanNumber = $vanList[$count] The query appears inside a loop hence the $vanList[$count] variable. I have a txt file that contains the van numbers. I read that file and then run through the loop and for each van number I execute the above statement. Yes, I've trimmed off any extra blank spaces before and after the 'read' van numbers... I want to SUM the grossPay for each van number. Here's what it should be: $1500 $200 But when I execute I get this: Resource id#3 Resource id#4 Anyone got any idea why this is happening? Thanks in advance, Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Need Help With MySQL Query
select vanNumber, sum(grossPay) from usertableDaily group by vanNumber; [EMAIL PROTECTED] wrote: hello, I've performed searches on this site and php.net to try and figure out why this is occuring. I can't find any instance in my searches that helped me. So, I'm posting my very first question to this list Here's my table ($usertableDaily): vanNumber|grossPay - | 1000 | 500 |100 |100 Here's my query: SELECT SUM(grossPay) FROM $usertableDaily WHERE vanNumber = $vanList[$count] The query appears inside a loop hence the $vanList[$count] variable. I have a txt file that contains the van numbers. I read that file and then run through the loop and for each van number I execute the above statement. Yes, I've trimmed off any extra blank spaces before and after the 'read' van numbers... I want to SUM the grossPay for each van number. Here's what it should be: $1500 $200 But when I execute I get this: Resource id#3 Resource id#4 Anyone got any idea why this is happening? Thanks in advance, Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Veysel Harun Sahin [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: Need Help With MySQL Query
Hi Interesting problem, normally to get a total by type, you would have a query like, select van, sum(pay) from ($usertableDaily) group by van; However you are individually quering each total, your approach is correct, but slower. The problem you have though is the return of Resource id#3 Resource id#4 So, to begin - What version of MySQL - What OS - What table type - Have you checked the table for corruption I would guess that you have some sort of table corruption. Please try repair table ($usertableDaily) and if that does fix it, email back with the rest of the data. All the best Simon On Saturday 15 February 2003 10:09 am, Guru Geek wrote: hello, I've performed searches on this site and php.net to try and figure out why this is occuring. I can't find any instance in my searches that helped me. So, I'm posting my very first question to this list Here's my table ($usertableDaily): vanNumber|grossPay - | 1000 | 500 |100 |100 Here's my query: SELECT SUM(grossPay) FROM $usertableDaily WHERE vanNumber = $vanList[$count] The query appears inside a loop hence the $vanList[$count] variable. I have a txt file that contains the van numbers. I read that file and then run through the loop and for each van number I execute the above statement. Yes, I've trimmed off any extra blank spaces before and after the 'read' van numbers... I want to SUM the grossPay for each van number. Here's what it should be: $1500 $200 But when I execute I get this: Resource id#3 Resource id#4 Anyone got any idea why this is happening? Thanks in advance, Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Simon Windsor Email: [EMAIL PROTECTED] Tel: 01454 617689 Mob: 07720 447385 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Need Help With MySQL Query
Are you referencing the result set correctly in php ? How are you dealing with what MySQL returns ? Looks like the info is there, you just not getting it out of the result set. - Jerry @ MetalCat.Net - - Original Message - From: Guru Geek [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 15, 2003 10:09 AM Subject: Need Help With MySQL Query hello, I've performed searches on this site and php.net to try and figure out why this is occuring. I can't find any instance in my searches that helped me. So, I'm posting my very first question to this list Here's my table ($usertableDaily): vanNumber|grossPay - | 1000 | 500 |100 |100 Here's my query: SELECT SUM(grossPay) FROM $usertableDaily WHERE vanNumber = $vanList[$count] The query appears inside a loop hence the $vanList[$count] variable. I have a txt file that contains the van numbers. I read that file and then run through the loop and for each van number I execute the above statement. Yes, I've trimmed off any extra blank spaces before and after the 'read' van numbers... I want to SUM the grossPay for each van number. Here's what it should be: $1500 $200 But when I execute I get this: Resource id#3 Resource id#4 Anyone got any idea why this is happening? Thanks in advance, Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
List
Is it just me or is everyone on the list getting junk/spam from naver.com ? - Jerry @ MetalCat.Net - sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error with libmyclient.a
I assume you use gcc, and your object file is :myclient.o and executable file : is myclient so try it: gcc -o myclient myclient.o -L/usr/lib/mysql -lmysqlclient -lz You can use 'mysql_config' utility to determine the proper flags for compiling and linking.Example: % mysql mysql_config --cflags -I' /usr/include/mysql' % mysql mysql_config --libs -L '/usr/lib/mysql' -lmysqlclient -lz -lcrypt -lnsl -lm You can download pdf format abaout C Api reference for complete description at Paul Dubois home page -- __ Sign-up for your own FREE Personalized E-mail at Mail.com http://www.mail.com/?sr=signup - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Full Text Search - Exact Matches Only ?
Hi
Well I asked about index's earlier and had the answer in 30 mins
So I have created an index on several columns and I call my search like
so:
strsql = SELECT * FROM global WHERE MATCH (SURNAME, GROOMSURNAME,
BRIDESURNAME, OTHERSURNAME, MOTHERMNAME ) AGAINST ('gold')
Problem now is the against term as it only brings back exact matches so
the above will only return the gold names I have tried adding a wildcard
after gold ie: gold* so it would bring back everything starting with
gold but to no avail.
I'm sure its syntax related but I'm stuck at the moment.
Regards
John Berman
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: List
Jerry, Is it just me or is everyone on the list getting junk/spam from naver.com ? We all have this problem :/ Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error No. 7
You probably have a permissions issue in your data or temp directory...Mysql is not able to create a temporary table when making the table alteration. Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: Insanely Great [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Saturday, February 15, 2003 12:16 AM Subject: Error No. 7 Hello When I use the Alter table structure command to alter any field, it comes up with an error stating that Error number 7 - error in renaming of table table name.myi to sql-208... and so on with (Errorcode 13). Thus I am unable to change the structure of any table. I am using MySQL ver 3.23.55 and on a Windows XP professional system. Any help. Insane - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Delete duplicate rows
Make a temporary table, SELECT DISTINCT the rows from the old table to the new table, rename the old table TABLE_OLD, rename the temporary table to the original table name. Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: Lewis Watson [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Saturday, February 15, 2003 12:30 AM Subject: Delete duplicate rows I need to delete duplicate rows. Each row that is in the table has an exact duplicate of itself. There are four columns. No one column could be defined as a primary key; however, two columns together could. What's going to be the best way to do this? Thanks. Lewis mysql, thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error with libmyclient.a
I think 'mysql_config' built in mysql RPM (binary) package. I use MySQL-3.23.55.1 and MySQL-3.23.33 under RedHat 7.2 and all are fine. You can download binary format (RPM) from http://www.mysql.com/downloads/mysql-3.23.html included : MySQL-3.23.55.1.rpm (server),MySQL-client,MySQL_devel, and MySQL-Shared(mysql.so) Sorry, I have wrote: %mysql mysql_config --libs it's wrong. it should be % mysql_config --libs % mysql_config --cflags I hope these help you. Untung - Original Message - From: Amy Joseph Kormann [EMAIL PROTECTED] Date: Sat, 15 Feb 2003 08:37:28 -0500 To: Untung Rasidin [EMAIL PROTECTED] Subject: Re: Error with libmyclient.a Untung Rasidin wrote: You can use 'mysql_config' utility to determine the proper flags for compiling and linking. Where is the mysql_config utility found? I have 9.23 distrib 3.22.7b-beta. -- Amy and Joseph Kormann -- __ Sign-up for your own FREE Personalized E-mail at Mail.com http://www.mail.com/?sr=signup - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: My dump does not work
lets try it: - first, create database : dbase_name and type it: % mysql -uusername -ppassword dbase_name /path/to/directory/yourdump.sql if anything wrong, please check yourdump.sql. May be incorrect syntax. - Original Message - From: kk [EMAIL PROTECTED] Date: Fri, 14 Feb 2003 17:10:28 +0900 To: list mysql [EMAIL PROTECTED] Subject: Re: My dump does not work When I faced the same problem, I was suggested to use MySQL Front. I have been also using phpMyAdmin.Both work fine. Hope this will help u regards - Original Message - From: srinath sundarrajan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 14, 2003 4:11 PM Subject: My dump does not work Hello, I face a problem in transfering a mysql db from 1 machine to another. I used the mysqldump command to take the dump of a db from machine A running linux. I put the same on to a machine B running on linux. Then I used the mysqldump command to create the database. In all 15 tables should be there. Unfortunately only 3 tabels are there. The dump restoration stops midway through the third table without giving any error. I tried opening the original .sql file in wordpad. All the tabels records are there. Any idea what could be the problem. Is there any way to transfer table by table. Machine B is a new one. Thanks regards, Srinivasan. _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 -- __ Sign-up for your own FREE Personalized E-mail at Mail.com http://www.mail.com/?sr=signup - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: creating a user with certain privileges
In our SQL database we want to create a user with certain privileges. Also we need to
only allow them to view certain tables not the entire lot.
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
Using the above syntax, by granting a person certain privleges does this mean that
they can only see the table and database that the privilege is given to? Also by
using username@'%' does this mean the username can login from anywhere. We need to
allow the user to login from a remote location via the Internet.
Thanks
Jon L. Miller, MCNE, CNS
Director/Sr Systems Consultant
MMT Networks Pty Ltd
http://www.mmtnetworks.com.au
I don't know the key to success, but the key to failure
is trying to please everybody. -Bill Cosby
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
NullPointerException in Connection
Hi, I'm having a quite serious problem. I wrote a web application that uses MySQL via ConnectorJ V2.0.6 in combination with Apache's DBCP 1.0 (Jakarta Commons Database Connection Pool). The web application is installed on a server, I don't have access to. The same for the MySQL server. Once in a while, I get a NullPointerException and have no clue about the reason: DBCP, the JDBC driver, or MySQL. Once the exception occurred, it comes up with every new connection retrieval. I really wonder about the NullPointerexception, which seems to me like a ConnectorJ-Bug, but I'm not sure at all. java.lang.NullPointerException at org.gjt.mm.mysql.Connection.execSQL(Connection.java:863) at org.gjt.mm.mysql.Connection.execSQL(Connection.java:857) at org.gjt.mm.mysql.Connection.setAutoCommit(Connection.java:474) at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnect ion.java:237) at org.apache.commons.dbcp.PoolableConnectionFactory.activateObject(PoolableCon nectionFactory.java:273) at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(Unknown Source) at org.apache.commons.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool .java:117) at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.ja va:110) at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:3 12) at org.apache.struts.util.GenericDataSource.getConnection(GenericDataSource.jav a:276) at de.mytest.fe.BaseAction.getConnection(BaseAction.java:36) Actually, I use a DataSource, provided by Struts 1.1b2, which in turn uses DBCP. The parameters for the DataSource are as follows: data-source set-property property=autoCommit value=true/ set-property property=description value=Data Source for MySQL/ set-property property=driverClass value=org.gjt.mm.mysql.Driver/ set-property property=maxCount value=10/ set-property property=minCount value=2/ set-property property=password value=***/ set-property property=url value=jdbc:mysql://localhost:3306/somedb?autoReconnect=true/ set-property property=user value=**/ set-property property=autoReconnect value=true/ /data-source (The double autoReconnect was used to make sure, DBCP auto reconnects. Since we have no access to the Servers, we couldn't figure out, which one does the job -- the one in the url, or the extra paramter. However, this is not the problem. AutoReconnect works). The responsibles from the server park (university owned) swear that they aren't doing anything wrong, whatever that means. So maybe the problem is there, who knows when you don't run your own servers. Does anybody can give me a hint on how to proceed? Time is running away and -- as usual -- the application should have been stable last week... Please send a CC to [EMAIL PROTECTED] Thanks for your time, Holger Virus checked by G DATA AntiVirusKit Virus news: www.antiviruslab.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 shutdown hangs-solaris
Hi, I am facing mysql shutdown process hanging problem on Solaris 5.8, mysql 3.23.40 I am running our product Web NMS which uses MySQL DB, on solaris machine. While shutting down the application, except MySQL process, all other process are killed and the application hangs. On analysis I found that, mysqladmin -h localhost shutdown is hanging. But when I tried to take the dump, the process (mysqladmin -h localhost shutdown) gets killed. Kindly provide us the cause of the problem. Herewith I have attached the files, ps_before_shutdown.txt- Processes list before shutting down the Web NMS ps_during_hang.txt- Processes list while application is hanging ps_after_ctrl_backslash.txt- Processes list after taking the dump(dump). core- dump Currently the machine does not have anything else running and I am connecting to it through Exceed (I tried both CDE and Xapplication displaying somewhere else) because I do not have a Sun monitor yet. Also the problem is not consistencly reproducible. Thanks a lot in advance. Regards Sunil Gavaskar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Delete duplicate rows
At 23:30 -0600 2/14/03, Lewis Watson wrote: I need to delete duplicate rows. Each row that is in the table has an exact duplicate of itself. There are four columns. No one column could be defined as a primary key; however, two columns together could. What's going to be the best way to do this? Thanks. Lewis mysql, thanks You can define those columns as a primary key with ALTER IGNORE TABLE and MySQL will remove the duplicate rows with duplicate primary key values. (The IGNORE is important, otherwise the statement will fail.) Here's an example that shows how it works. mysql CREATE TABLE t (i INT NOT NULL, j INT NOT NULL); mysql INSERT INTO t (i,j) VALUES(1,1); mysql INSERT INTO t (i,j) VALUES(1,1); mysql INSERT INTO t (i,j) VALUES(1,2); mysql INSERT INTO t (i,j) VALUES(1,2); mysql INSERT INTO t (i,j) VALUES(2,2); mysql SELECT * FROM t; +---+---+ | i | j | +---+---+ | 1 | 1 | | 1 | 1 | | 1 | 2 | | 1 | 2 | | 2 | 2 | +---+---+ mysql ALTER IGNORE TABLE t ADD PRIMARY KEY (i,j); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 2 Warnings: 0 mysql SELECT * FROM t; +---+---+ | i | j | +---+---+ | 1 | 1 | | 1 | 2 | | 2 | 2 | +---+---+ If you don't want to keep the index, drop it: mysql ALTER TABLE t DROP PRIMARY KEY; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: NullPointerException in Connection
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Holger Wiechert wrote: Hi, I'm having a quite serious problem. I wrote a web application that uses MySQL via ConnectorJ V2.0.6 in combination with Apache's DBCP 1.0 (Jakarta Commons Database Connection Pool). The web application is installed on a server, I don't have access to. The same for the MySQL server. Once in a while, I get a NullPointerException and have no clue about the reason: DBCP, the JDBC driver, or MySQL. Once the exception occurred, it comes up with every new connection retrieval. I really wonder about the NullPointerexception, which seems to me like a ConnectorJ-Bug, but I'm not sure at all. java.lang.NullPointerException at org.gjt.mm.mysql.Connection.execSQL(Connection.java:863) at org.gjt.mm.mysql.Connection.execSQL(Connection.java:857) at org.gjt.mm.mysql.Connection.setAutoCommit(Connection.java:474) Connector/J Package Names start with com.mysql.jdbc This is not MySQL Connector/J, it is MM.MySQL, which is what MySQL Connector/J used to be called (and a rather old version at that). Please try upgrading to MySQL Connector/J 2.0.14 or 3.0.5 from http://www.mysql.com/products/connector-j/ and see if your problem goes away. -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+TmqutvXNTca6JD8RAowMAKDIgKYPMtNZhsJUleTlqodGvMAoLACgjY8I 6I2n+jc+Y3D1cWc0C50XQaQ= =UdFn -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
Install from tarball fails
Description: Attempted to follow instructions to install from source tarball as indicated in manual.pdf. I acquired manual.pdf from: http://www.mysql.com/Documents/Manual/manual.pdf On Page 81 of manual.pdf I followed the 'To install a source distribution' Steps 1 through 6 went fine. However at step 7 that is where the install process halted. Step 1 - Pick directory to install distribution and move into it. # cd /usr/local Step 2 - Obtain a distribution file from one of the sites listed: Acquired 'mysql-max-3.23.55-pc-linux-i686.tar.gz' Step 3 - Berkeley DB tables disclaimer. Not interested at this time. Step 4 - Add a user and group for mysqld to run as: # created user mysql with uid of 114 and group mysql with gid of 108 Step 5 - Unpack the distribution into current directory: # tar -zxvf /tmp/mysql-max-3.23.55-pc-linux-i686.tar.gz Step 6 - Change into the top-level directory of the unpacked distribution: # cd mysql-max-3.23.55-pc-linux-i686 Step 7 - Configure the release and compile everything: # ./configure --prefix=/usr/local/mysql This yielded: = NOTE: This is a MySQL binary distribution. It's ready to run, you don't need to configure it! To help you a bit, I am now going to create the needed MySQL databases and start the MySQL server for you. If you run into any trouble, please consult the MySQL manual, that you can find in the Docs directory. Installing all prepared tables 030215 8:10:53 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h cvlxit2 password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the ./bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can start the MySQL daemon with: cd . ; ./bin/safe_mysqld You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com Starting the mysqld server. You can test that it is up and running with the command: ./bin/mysqladmin version cvlxit2:/usr/local/mysql-max-3.23.55-pc-linux-i686# Starting mysqld daemon with databases from /usr/local/mysql-max-3.23.55-pc-linux-i686/data 030215 08:10:53 mysqld ended = Hmmm, well I acquired a source distribution and I ended up with a pre-compiled version. Oh well no matter, I guess I will continue on: So the output of 'configure' indicated to: = ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h cvlxit2 password 'new-password' = So I executed: ./bin/mysqladmin -u root password 'password' This yielded: = ./bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! = Hmmm, oh well lets continue on with the instructions from 'configure', so I executed: ./bin/mysqladmin -u root -h cvlxit2 password 'new-password' This yielded: = ./bin/mysqladmin: connect to server at 'cvlxit2' failed error: 'Can't connect to MySQL server on 'cvlxit2' (111)' Check that mysqld is running on cvlxit2 and that the port is 3306. You can check this by doing 'telnet cvlxit2 3306' = Hmmm, so mysqld is apparently not running, I confirmed this with the above example of telnet'ing to port 3306 and by executing 'ps -aef | grep -i mysql'. Well obviously something was seriously wrong, so I backed up in 'manual.pdf' to page 77 and started on the section '2.2.7 Installing a MySQL Binary Distribution'. On page 78 a detailed description of the steps required begins: Step 1 - Pick directory /usr/local Step 2 - Obtain distribution file from one of the sites Apparently I already have a distribution file. Step 3 - max suffix, Yes, then see Section 4.7.5 Section 4.7.5 indicates that mysqld-max is the MySQL server (mysqld) configured with the following options: -with-server-suffix=-max -with-innodb -with-bdb CFLAGS=-DUSE_SYMDIR Also indicates that
Report a bug
if you have a file NameOfFile.sql where you have a table named Aux and you try to create it using: mysql -u USER -p DATABASE NameOfFile.sql when it reaches the definition of table Aux, this will block the execution of the command. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Report a bug
At 17:44 + 2/15/03, Pedro Vasconcelos wrote: if you have a file NameOfFile.sql where you have a table named Aux and you try to create it using: mysql -u USER -p DATABASE NameOfFile.sql when it reaches the definition of table Aux, this will block the execution of the command. I have no problem creating a table named Aux, there's probably something wrong with the statement other than the table name. sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
import 'decimal' data to int(11)
Hello All: using MYSQL Ver 11.15 Distrib 3.23.41, for redhat-linux-gnu (i386) (RH 7.2) I have data exported from an excel spreadsheet as tab delimited text. Certain columns need to go to mysql table columns with a type of int(11). (alternative datatypes will be considered) The data from the spreadsheet have formats that include enclosed quotes, dollar signs commas and are of a decimal format. Using vim, I can safely remove the quotes and the dollar signs: as in $2,456.12345 - 2,456.12345 I am afraid that attempting to use vim to remove the commas will have unpredictable side effects in other columns. 1)Will 2,456.12345 import safely into an int(11) column? If not, may an alternative datatype be recomended? NOTE: the number that I want to have (using the example above) would be 2456 rounded to the nearest 'whole number'. Any help is appreciated. Pointers to documentation is welcome as well. I hope I've provided enough info here. regards -- 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: Report a bug
The NameOfFile.sql contains: # # Table structure for table 'Aux' # DROP TABLE IF EXISTS Aux; CREATE TABLE Aux ( PrecoBase decimal(40,2) default NULL ) TYPE=MyISAM; # # Dumping data for table 'Aux' # LOCK TABLES Aux WRITE; INSERT INTO Aux VALUES (100.00),(100.11),(100.00),(100.22),(101.00),(102.00); UNLOCK TABLES; when i try this command: mysql -u USER -p DATABASE NameOfFile.sql (after i insert the password) it blocks. I am using 3.23.55 in win2000 On Sat, 15 Feb 2003, Paul DuBois wrote: At 17:44 + 2/15/03, Pedro Vasconcelos wrote: if you have a file NameOfFile.sql where you have a table named Aux and you try to create it using: mysql -u USER -p DATABASE NameOfFile.sql when it reaches the definition of table Aux, this will block the execution of the command. I have no problem creating a table named Aux, there's probably something wrong with the statement other than the table name. sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: import 'decimal' data to int(11)
At 9:05 -0900 2/15/03, Tim Johnson wrote:
Hello All:
using MYSQL Ver 11.15 Distrib 3.23.41, for redhat-linux-gnu
(i386) (RH 7.2)
I have data exported from an excel spreadsheet as tab delimited text.
Certain columns need to go to mysql table columns with a type of int(11).
(alternative datatypes will be considered)
The data from the spreadsheet have formats that include
enclosed quotes, dollar signs commas and are of a decimal
format. Using vim, I can safely remove the quotes and the
dollar signs:
as in $2,456.12345 - 2,456.12345
I am afraid that attempting to use vim to remove the
commas will have unpredictable side effects in other
columns.
1)Will 2,456.12345 import safely into an int(11) column?
If not, may an alternative datatype be recomended?
No, values with commas will not import correctly into any numeric column.
NOTE: the number that I want to have (using the example above)
would be 2456 rounded to the nearest 'whole number'.
Any help is appreciated. Pointers to documentation is welcome
as well. I hope I've provided enough info here.
Import the values into a character column, then use the REPLACE()
function to eliminate the unwanted characters. You can do this with the
dollar signs as well if you like.
mysql CREATE TABLE t (num CHAR(30));
mysql INSERT INTO t (num) VALUES('$2,456.12345');
mysql SELECT num FROM t;
+--+
| num |
+--+
| $2,456.12345 |
+--+
mysql UPDATE t SET num = REPLACE(num,'$','');
mysql SELECT num FROM t;
+-+
| num |
+-+
| 2,456.12345 |
+-+
mysql UPDATE t SET num = REPLACE(num,',','');
mysql SELECT num FROM t;
++
| num|
++
| 2456.12345 |
++
mysql ALTER TABLE t MODIFY num INT;
mysql SELECT num FROM t;
+--+
| num |
+--+
| 2456 |
+--+
If you're using LOAD DATA to import the file, you can probably
add a FIELDS ENCLOSED BY '' clause, and then you won't even
need to strip the double quotes.
regards
--
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: Report a bug
At 18:10 + 2/15/03, Pedro Vasconcelos wrote: The NameOfFile.sql contains: # # Table structure for table 'Aux' # DROP TABLE IF EXISTS Aux; CREATE TABLE Aux ( PrecoBase decimal(40,2) default NULL ) TYPE=MyISAM; # # Dumping data for table 'Aux' # LOCK TABLES Aux WRITE; INSERT INTO Aux VALUES (100.00),(100.11),(100.00),(100.22),(101.00),(102.00); UNLOCK TABLES; when i try this command: mysql -u USER -p DATABASE NameOfFile.sql (after i insert the password) it blocks. I am using 3.23.55 in win2000 It works fine on Unix. Is Aux a special filename in Windows? On Sat, 15 Feb 2003, Paul DuBois wrote: At 17:44 + 2/15/03, Pedro Vasconcelos wrote: if you have a file NameOfFile.sql where you have a table named Aux and you try to create it using: mysql -u USER -p DATABASE NameOfFile.sql when it reaches the definition of table Aux, this will block the execution of the command. I have no problem creating a table named Aux, there's probably something wrong with the statement other than the table name. sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Report a bug
It works fine on Unix. Is Aux a special filename in Windows? Not that i am aware of. Maybe you should try this on Windows. I could be just a problem on windows version. Thanks. On Sat, 15 Feb 2003, Paul DuBois wrote: At 18:10 + 2/15/03, Pedro Vasconcelos wrote: The NameOfFile.sql contains: # # Table structure for table 'Aux' # DROP TABLE IF EXISTS Aux; CREATE TABLE Aux ( PrecoBase decimal(40,2) default NULL ) TYPE=MyISAM; # # Dumping data for table 'Aux' # LOCK TABLES Aux WRITE; INSERT INTO Aux VALUES (100.00),(100.11),(100.00),(100.22),(101.00),(102.00); UNLOCK TABLES; when i try this command: mysql -u USER -p DATABASE NameOfFile.sql (after i insert the password) it blocks. I am using 3.23.55 in win2000 It works fine on Unix. Is Aux a special filename in Windows? On Sat, 15 Feb 2003, Paul DuBois wrote: At 17:44 + 2/15/03, Pedro Vasconcelos wrote: if you have a file NameOfFile.sql where you have a table named Aux and you try to create it using: mysql -u USER -p DATABASE NameOfFile.sql when it reaches the definition of table Aux, this will block the execution of the command. I have no problem creating a table named Aux, there's probably something wrong with the statement other than the table name. sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Install from tarball fails
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: Description: Attempted to follow instructions to install from source tarball as indicated in manual.pdf. I acquired manual.pdf from: http://www.mysql.com/Documents/Manual/manual.pdf On Page 81 of manual.pdf I followed the 'To install a source distribution' Steps 1 through 6 went fine. However at step 7 that is where the install process halted. Step 1 - Pick directory to install distribution and move into it. # cd /usr/local Step 2 - Obtain a distribution file from one of the sites listed: Acquired 'mysql-max-3.23.55-pc-linux-i686.tar.gz' Step 3 - Berkeley DB tables disclaimer. Not interested at this time. Step 4 - Add a user and group for mysqld to run as: # created user mysql with uid of 114 and group mysql with gid of 108 Step 5 - Unpack the distribution into current directory: # tar -zxvf /tmp/mysql-max-3.23.55-pc-linux-i686.tar.gz If the tar archive filename has processor and operating system information in it, it is a binary distribution, not a source distribution. On the downloads page, the different downloads are broken out into platform-specific binary versions, and then at the very end a 'Source Distributions' section. The rest of your problems you list mostly have to do with this initial misunderstanding...none of the directions given in the manual will correspond with what you are seeing if you downloaded a binary instead of a source distribution :) Do you have any feedback as to where our documentation or download pages might have led you to believe you were downloading a source distribution when you were actually downloading a binary distribution, so that we may fix them? -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+ToWBtvXNTca6JD8RAjArAKCadahK0pVckYh9TxW3KXbEoKfr5gCfd9cX 5r6VIHntSoo+moSQUb2aTwE= =nHWs -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: Report a bug
Hi, AFAIK, Aux is indeed a special filename in Windows (if I execute Aux under the W2K console, it opens the open with window). Regards, Jocelyn - Original Message - From: Pedro Vasconcelos [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, February 15, 2003 6:20 PM Subject: Re: Report a bug It works fine on Unix. Is Aux a special filename in Windows? Not that i am aware of. Maybe you should try this on Windows. I could be just a problem on windows version. Thanks. On Sat, 15 Feb 2003, Paul DuBois wrote: At 18:10 + 2/15/03, Pedro Vasconcelos wrote: The NameOfFile.sql contains: # # Table structure for table 'Aux' # DROP TABLE IF EXISTS Aux; CREATE TABLE Aux ( PrecoBase decimal(40,2) default NULL ) TYPE=MyISAM; # # Dumping data for table 'Aux' # LOCK TABLES Aux WRITE; INSERT INTO Aux VALUES (100.00),(100.11),(100.00),(100.22),(101.00),(102.00); UNLOCK TABLES; when i try this command: mysql -u USER -p DATABASE NameOfFile.sql (after i insert the password) it blocks. I am using 3.23.55 in win2000 It works fine on Unix. Is Aux a special filename in Windows? On Sat, 15 Feb 2003, Paul DuBois wrote: At 17:44 + 2/15/03, Pedro Vasconcelos wrote: if you have a file NameOfFile.sql where you have a table named Aux and you try to create it using: mysql -u USER -p DATABASE NameOfFile.sql when it reaches the definition of table Aux, this will block the execution of the command. I have no problem creating a table named Aux, there's probably something wrong with the statement other than the table name. sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Report a bug
Ok :) You are absolutely correct. My mistake :) Bye On Sat, 15 Feb 2003, Jocelyn Fournier wrote: Hi, AFAIK, Aux is indeed a special filename in Windows (if I execute Aux under the W2K console, it opens the open with window). Regards, Jocelyn - Original Message - From: Pedro Vasconcelos [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, February 15, 2003 6:20 PM Subject: Re: Report a bug It works fine on Unix. Is Aux a special filename in Windows? Not that i am aware of. Maybe you should try this on Windows. I could be just a problem on windows version. Thanks. On Sat, 15 Feb 2003, Paul DuBois wrote: At 18:10 + 2/15/03, Pedro Vasconcelos wrote: The NameOfFile.sql contains: # # Table structure for table 'Aux' # DROP TABLE IF EXISTS Aux; CREATE TABLE Aux ( PrecoBase decimal(40,2) default NULL ) TYPE=MyISAM; # # Dumping data for table 'Aux' # LOCK TABLES Aux WRITE; INSERT INTO Aux VALUES (100.00),(100.11),(100.00),(100.22),(101.00),(102.00); UNLOCK TABLES; when i try this command: mysql -u USER -p DATABASE NameOfFile.sql (after i insert the password) it blocks. I am using 3.23.55 in win2000 It works fine on Unix. Is Aux a special filename in Windows? On Sat, 15 Feb 2003, Paul DuBois wrote: At 17:44 + 2/15/03, Pedro Vasconcelos wrote: if you have a file NameOfFile.sql where you have a table named Aux and you try to create it using: mysql -u USER -p DATABASE NameOfFile.sql when it reaches the definition of table Aux, this will block the execution of the command. I have no problem creating a table named Aux, there's probably something wrong with the statement other than the table name. sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Question about IN operator
I have a field (cdfeature) that contains a list of comma seperated values. Example follows: select item_id, cdfeature from cars where (101 in (cdfeature)); +-+-+ | item_id | cdfeature | +-+-+ | 320 | 101,104,106 | | 321 | 101,103,105,112 | | 323 | 101,103,105,112 | | 316 | 101,102,104,108,112 | | 345 | 101,102,104,108,112 | +-+-+ When I select only one value (ie, 101) the statement works. I need to be able to select based upon multinple IN statements. When I try this, the query returns an empty set. Example follows: select item_id, cdfeature from cars where (101 in (cdfeature)) and (112 in (cdfeature)); Empty set (0.00 sec) The above query should have, I think, returned records that had BOTH a 101 an a 112 in the cdfeature filed. Specifically, it should have returned records 321, 323, 316, 345. Somebody please help me out here!!! Don! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 replication and corrupting tables
Hi all, I have a problem with replication, that while repeatable for me very easily, I can not come up with a way for others to repeat it without all our tables and binlogs (tens of gigabytes). So I'm simply going to describe things here and see if anyone else has experienced anything similar or might have some suggestions. After thinking about using replication, for what seems like forever, I finally got around to it. Both the master and the slave are v4.0.10. I started it up and all seemed to work well for a while. Maybe a few hours. Then I found that a table got corrupted on the slave: ERROR: 1034 Incorrect key file for table: 'forums_posts_new_0'. Try to repair it 030215 10:01:12 Slave: error 'Incorrect key file for table: 'forums_posts_new_0'. Try to repair it' on query 'insert into forums_posts_new_0... Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'binlog.003' position 97273308 At this point the slave SQL thread stopped. The IO thread continued. A couple of days later I noticed the error, repaired the table and started the slave thread again. With the IO thread so far ahead, the SQL thread could pump through the queries much faster. Now it only takes 3-4 minutes before another table gets corrupted. However, it is not just any table. I have tables 'forums_posts_new_0' to 'forums_posts_new_9' that hold messages. Out of all the tables, only these get corrupted. If I repair the table, then start the slave it will work for 5-15 minutes until another table is corrupted. Repeat. Repeat. Repeat. I checked the drives and the file system for errors and found no problems. The machine that acts as a slave to the master is also used for data-warehouse and FTS operations, has lots of disk access on its database and has no errors. I have tried stopping data warehouse and FTS operations while the slave runs, but it makes no difference. BTW: Sometimes the slave crashes when doing replication (and in the following example, only replication). Example of a backtrace: 0x806f53b handle_segfault + 447 0x826ae18 pthread_sighandler + 184 0x8296b07 memcpy + 39 0x823703d _mi_balance_page + 649 0x8236994 _mi_insert + 392 0x82367d2 w_search + 518 0x8236793 w_search + 455 0x8236793 w_search + 455 0x8236793 w_search + 455 0x8236793 w_search + 455 0x8236482 _mi_ck_write_btree + 142 0x82363e9 _mi_ck_write + 65 0x823602f mi_write + 591 0x80c257d write_row__9ha_myisamPc + 101 0x80a17f5 write_record__FP8st_tableP12st_copy_info + 513 0x80a110d mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt4List1Z4Item 15enum_duplicates + 1129 0x807ad7a mysql_execute_command__Fv + 6598 0x807d226 mysql_parse__FP3THDPcUi + 146 0x80add97 exec_event__15Query_log_eventP17st_relay_log_info + 427 0x80e3faa exec_relay_log_event__FP3THDP17st_relay_log_info + 542 0x80e4aca handle_slave_sql + 602 0x82685cc pthread_start_thread + 220 0x829dd8a thread_start + 4 After the above crash, mysqld restarted and the slave continued to run for a while without error. Weird. For a while... -steve- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Red Hat 8.0 compile problems with 4.0.10
On 2/15/03 1:25 AM, Duncan Maitland wrote: I am having trouble compiling a custom mysqld (version 4.0.10) under Red Hat 8.0 - I did email this list a few weeks ago and I have tried a few things since then but I am asking again in the hope that someone more knowledgeable might be able to help. This is a known problem (in that I've seen several people post about it, including myself), but I have not seen any answers other than use the binary distribution or binary RPMS. I have successfully tested the RPM distribution, but I would prefer to use the source. As far as I can tell this is a compiling or linking problem. From what I've been able to figure out, the MySQL server will start up, but when you attempt to connect to it, it seg vaults. I haven't had time to track it down, but it doesn't appear to be a simple resolver library problem, as I've seen suggested. Also, 4.0.10 is increases the thread-stack size, supposedly to fix this (or maybe a related) problem. It hasn't helped, as far as I can tell. Anyway, I would be interested in an answer to this as well. -m -- ## Mark T. Dame, Vice President, Internet Operations ## MFM Communication Software: http://www.mfm.com/ ## E-mail: mailto:[EMAIL PROTECTED] WWW: http://www.mfm.com/~mdame/ Compassion -- that's the one thing no machine ever had. Maybe it's the one thing that keeps men ahead of them. -- Star Trek: Dr. McCoy, The Ultimate Computer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question about IN operator
At 12:41 -0600 2/15/03, Don! Briggs wrote: I have a field (cdfeature) that contains a list of comma seperated values. Example follows: select item_id, cdfeature from cars where (101 in (cdfeature)); +-+-+ | item_id | cdfeature | +-+-+ | 320 | 101,104,106 | | 321 | 101,103,105,112 | | 323 | 101,103,105,112 | | 316 | 101,102,104,108,112 | | 345 | 101,102,104,108,112 | +-+-+ When I select only one value (ie, 101) the statement works. I need to be able to select based upon multinple IN statements. When I try this, the query returns an empty set. Example follows: select item_id, cdfeature from cars where (101 in (cdfeature)) and (112 in (cdfeature)); Empty set (0.00 sec) The above query should have, I think, returned records that had BOTH a 101 an a 112 in the cdfeature filed. Specifically, it should have returned records 321, 323, 316, 345. Somebody please help me out here!!! That's not how IN() works. It takes an argument list consisting of a set of values, separated by commas. You're providing a single value that contains commas internally. That's not the same thing at all. If there are 64 or fewer cdfeature values, you might consider representing that column as a SET column. Don! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INSERTs getting slower with growing tabel, using no indexes?
Hello I have a simple table consisting of a couple of columns with int-values and one datetime column. The table doesnt have any indexes and consists of over 10 million records and are growing with more then 200 000 records per day. Even though I am NOT using an index the time for inserting new rows takes longer and longer the larger the table become. I find this strange since the data isnt organized the time for inserting a row should be constant regardless of the size of the table. Is it I that is wrong or is there a way to get the time for insert queries to be of constant time regardless of the size of the table? /Magnus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 broken compiled with -all-static
Hi,
I'm trying to compile mysql 3.23.55.
Some info:
uname -a
Linux atlas 2.4.20 #1 Sat Feb 1 10:39:59 CET 2003 i586 unknown
glibc with libpthreads 2.3.1
gcc -v
Reading specs from /usr/lib/gcc-lib/i586-pc-linux-gnu/3.2.1/specs
Configured with: ./configure --prefix=/usr --sysconfdir=/etc --localstatedir=/var
--enable-threads=posix --with-gnu-ld --enable-shared --enable-languages=c,c++
Thread model: posix
gcc version 3.2.1
Compilation options:
CFLAGS=-O3 -mcpu=k6-2 CXX=gcc CXXFLAGS=-O3 -mcpu=k6-2 -felide-constructors
-fno-exceptions -fno-rtti ./configure --prefix=/usr/mysql --enable-thread-safe-client
--enable-local-infile --enable-assembler --with-berkeley-db --with-gnu-ld
--libdir=/usr/lib --includedir=/usr/include --mandir=/usr/man
--with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --disable-shared
No erros, and compilation successfull.
But mysqld seems to be broken.
When making mysqld_install_db:
-
./scripts/mysql_install_db
Preparing db table
Preparing host table
Preparing user table
Preparing func table
Preparing tables_priv table
Preparing columns_priv table
Installing all prepared tables
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail
key_buffer_size=8388600
record_buffer=131072
sort_buffer=2097144
max_used_connections=0
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 225791 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Stack range sanity check OK, backtrace follows:
0x808f4d4
0x81bc4ba
0x81ce98d
0x8096cf6
0x8099310
0x8091f48
Stack trace seems successful - bottom reached
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x8312678 = CREATE TABLE db ( Host char(60) binary DEFAULT '' NOT
NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT
NULL, Select_priv enum('N','Y') DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y')
DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') DEFAULT 'N' NOT NULL, Delete_priv
enum('N','Y') DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') DEFAULT 'N'
NOT NULL, References_priv enum('N','Y') DEFAULT 'N' NOT NULL, Index_priv
enum('N','Y') DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL,
PRIMARY KEY Host (Host,Db,User), KEY User (User) ) comment='Database privileges'
thd-thread_id=1
Successfully dumped variables, if you ran with --log, take a look at the
details of what thread 1 did to cause the crash. In some cases of really
bad corruption, the values shown above may be invalid
...
--
Resolving stack trace:
0x808f4d4 _Z15handle_segfaulti + 404
0x81bc67a __pthread_sighandler + 122
0x81ceb4d __strtol_internal + 1421
0x8096cf6 _Z17add_field_to_listPc16enum_field_typesS_S_jP4ItemS_P10st_typelib + 310
0x8099310 _Z7yyparsev + 3376
0x8091f48 _Z16handle_bootstrapPv + 648
Any help would be apreciated.
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
Query relational tables
Hi, I have a table that contains ids for several other tables. Setup is as followed: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | id | int(11) | | PRI | NULL|auto_increment | | title| varchar(100) | YES | | NULL|| | rateID | int(10) unsigned | YES | | NULL|| | actorID | int(10) unsigned | YES | | NULL|| | actor2ID | int(10) unsigned | YES | | NULL|| | actor3ID | int(10) unsigned | YES | | NULL|| +--+--+--+-+-++ +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | actorID | int(10) unsigned | | PRI | NULL| auto_increment | | first | varchar(20) | YES | | NULL|| | middle | varchar(20) | YES | | NULL|| | last| varchar(20) | YES | | NULL|| +-+--+--+-+-++ +--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | rateID | int(10) unsigned | | PRI | NULL| auto_increment | | rating | varchar(15) | YES | | NULL|| ++--+--+-+-++ How can I query the movie tables so that it will get the names of the actors when each actor field may have a different name or may be null? I am unable to query on more than one actor. Thanks Dee - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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[2]: Report a bug
Paul, Pedro, INSERT INTO Aux VALUES (100.00),(100.11),(100.00),(100.22),(101.00),(102.00); It works fine on Unix. Is Aux a special filename in Windows? Good guess! Here's what I get: C:\mysql\binecho hallo aus C:\mysql\binecho hallo aux Zugriff verweigert This is access denied. Looks like aux is a device name, such as lpt or con. I know that Aux used to be a hardware interface in hifi components, I didn't know it was a device name under Windows. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[4]: Cannot Connect
RBE, Sorry, but this doesn't answer my question. You said you used MySQL Monitor (the command line tool) when trying to connect to the server. When you started MySQL Monitor, how did you do that? Did you just double-click mysql (the MySQL Monitor) in some graphical file manager tool, or did you cd to the directory where mysql is sitting, and then type mysql? I put a desktop icon link to the directory where mysqlcc was located and it asks for a connection. When I click on the connection, I am asked for a user and password, but no password or user combination works. I've also done the command line option mysql -urbe -ppassword but the connection is refused with error 2002. Error 2002, strange. When the MySQL server is down, you should get error 2003, but maybe that's different with MySQLCC. To track down the problem, try as follows: 1) Make sure the MySQL server is running. Do something like ps xa | grep mysqld. If it's not running, try to start it like this: mysqld. This should report errors on startup, telling you where the problem lies. 2) If the problem reported says something about host.frm missing, you forgot the post-installation duties, i.e. to set up the grant tables. Here's how you do that: http://www.mysql.com/doc/en/Post-installation.html 3) If it's running, you have a privilege problem. If your MySQL installation is new, you'll have just some basic users set up. One of them is called root, who can connect from localhost without password. Thus, try this: mysql -uroot. Hope this helps. BTW, please keep the discussion on the list! Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re[4]: Cannot Connect
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Saturday 15 February 2003 3:03 pm, Stefan Hinz wrote: RBE, Sorry, but this doesn't answer my question. You said you used MySQL Monitor (the command line tool) when trying to connect to the server. When you started MySQL Monitor, how did you do that? Did you just double-click mysql (the MySQL Monitor) in some graphical file manager tool, or did you cd to the directory where mysql is sitting, and then type mysql? I put a desktop icon link to the directory where mysqlcc was located and it asks for a connection. When I click on the connection, I am asked for a user and password, but no password or user combination works. I've also done the command line option mysql -urbe -ppassword but the connection is refused with error 2002. Error 2002, strange. When the MySQL server is down, you should get error 2003, but maybe that's different with MySQLCC. To track down the problem, try as follows: 1) Make sure the MySQL server is running. Do something like ps xa | grep mysqld. If it's not running, try to start it like this: mysqld. This should report errors on startup, telling you where the problem lies. mysqld is running. 2) If the problem reported says something about host.frm missing, you forgot the post-installation duties, i.e. to set up the grant tables. Here's how you do that: http://www.mysql.com/doc/en/Post-installation.html No reported problem. 3) If it's running, you have a privilege problem. If your MySQL installation is new, you'll have just some basic users set up. One of them is called root, who can connect from localhost without password. Thus, try this: mysql -uroot. mysql -uroot gives me the following error message: ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) - -- Robert Black Eagle (DS) Get my key from http://www.keyserver.net/en Trying to copyright bits is like trying to make water dry. But politicians try, anyway. -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE+TrmatjSYKkYJrmcRAoyfAJ0dViac9pCbzXgrN2JW0ooumSSkoACbB5ky Znw467FvLJwxGMxfeWob1Fw= =slla -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
conditional default value
Hello All: (using MYSQL Ver 11.15 Distrib 3.23.41 for redhat-linux-gnu (i386) (RH 7.2)) I am importing text data (but in a numeric format). The customers wants 'N/A' in an empty field. Additionally: The customer wishes that if a field = '' the default value of 'N/A' be substituted. Is this possible to do in MySQL? Documentation? Regards -- 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[6]: Cannot Connect
Robert, 3) If it's running, you have a privilege problem. If your MySQL installation is new, you'll have just some basic users set up. One of them is called root, who can connect from localhost without password. Thus, try this: mysql -uroot. mysql -uroot gives me the following error message: ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Okay, my guess was wrong. It's not a privilege problem, it's a socket problem. You can specify the socket through which to connect when under Unix and connecting via localhost in a number of ways: a) In one of the my.cnf files, b) as a server start option on the command line, c) in a script that starts the server with an appropriate option. For a), you could look into /etc/my.cnf; maybe there's something wrong. You could also check ~/.my.cnf. Look for socket entries both in the [mysqld] and [client] sections. b) It's pretty unlikely that you start the server with a wrong socket option manually. c) There's a good chance that a start script will start the server with an option that's not apt to your system setup. Look for the script mysqld_safe and check for socket. d) Maybe mysqld can't create a socket in /var/lib/mysql/. Check the file permissions for this directory. If everything fails and you can't find a solution for your problem, you can connect via TCP/IP. In this case, specify the host and DON'T use localhost as host name, but rather the name of your machine. Suppose it's called 'machine', you would connect like this: mysql -hmachine -uroot (Frankly said, I'm not a Unix user, so others might be a better help if this can't solve your problems.) Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: conditional default value
At 14:00 -0900 2/15/03, Tim Johnson wrote: Hello All: (using MYSQL Ver 11.15 Distrib 3.23.41 for redhat-linux-gnu (i386) (RH 7.2)) I am importing text data (but in a numeric format). The customers wants 'N/A' in an empty field. Additionally: The customer wishes that if a field = '' the default value of 'N/A' be substituted. Is this possible to do in MySQL? Sure, but not with LOAD DATA. You can either preprocess the data file beforehand, or do this after import: UPDATE TABLE t SET col = 'N/A' IF col = '' OR col = ''; Documentation? Regards -- 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: conditional default value
Tim, I am importing text data (but in a numeric format). The customers wants 'N/A' in an empty field. Additionally: The customer wishes that if a field = '' the default value of 'N/A' be substituted. Is this possible to do in MySQL? Documentation? AFAIK there's no way to convert data at the time you import them. Afterwards, you can convert them as much as you like, or - as an alternative - leave them as they are, but convert them at display time. Just to give you an idea (suppose col is your field): SELECT IF(col='','N/A',col) /* conversion at display time */; You could also use CASE / WHEN / THEN / ELSE. Documentation: http://www.mysql.com/doc/en/Control_flow_functions.html Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql Digest 15 Feb 2003 14:31:06 -0000 Issue 2312
I am using mysql Ver 12.16 Distrib 4.0.7-gamma, for pc-linux-gnu (i686) I created the following table without error. The index line should have prevented the success of the command. CREATE TABLE IF NOT EXISTS catalog.junk ( hat VARCHAR(14) DEFAULT '' NOT NULL, hats VARCHAR(14) DEFAULT '' NOT NULL, house VARCHAR(14) DEFAULT '' NOT NULL, houses VARCHAR(14) DEFAULT '' NOT NULL, KEY hats (hats,house,house,house,house,house) ) TYPE = ISAM; As you can see the field `house' is repeated in the `KEY' line Barry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: INSERTs getting slower with growing tabel, using no indexes?
What type of platform/file system are you using? Very large files on filesystems can suffer from inode indirection problems...but I wouldn't think they'd be too noticeable. Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: Magnus Själander [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 15, 2003 2:08 PM Subject: INSERTs getting slower with growing tabel, using no indexes? Hello I have a simple table consisting of a couple of columns with int-values and one datetime column. The table doesn't have any indexes and consists of over 10 million records and are growing with more then 200 000 records per day. Even though I am NOT using an index the time for inserting new rows takes longer and longer the larger the table become. I find this strange since the data isn't organized the time for inserting a row should be constant regardless of the size of the table. Is it I that is wrong or is there a way to get the time for insert queries to be of constant time regardless of the size of the table? /Magnus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Report a bug
On 15-Feb-2003 Jocelyn Fournier wrote: Hi, AFAIK, Aux is indeed a special filename in Windows (if I execute Aux under the W2K console, it opens the open with window). MS-DOS, actually. Unix has 3 pre-defined file streams: stdin, stdout, and stderr. MS-DOS has the same three, plus two more: AUX and PRN. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql Digest 15 Feb 2003 14:31:06 -0000 Issue 2312
At 18:13 -0500 2/15/03, Barry Pollock wrote: I am using mysql Ver 12.16 Distrib 4.0.7-gamma, for pc-linux-gnu (i686) I created the following table without error. The index line should have prevented the success of the command. It may not make any sense, true, but it's not syntacticallyy invalid. You'll probably also find that you can create the same index multiple times. CREATE TABLE IF NOT EXISTS catalog.junk ( hat VARCHAR(14) DEFAULT '' NOT NULL, hats VARCHAR(14) DEFAULT '' NOT NULL, house VARCHAR(14) DEFAULT '' NOT NULL, houses VARCHAR(14) DEFAULT '' NOT NULL, KEY hats (hats,house,house,house,house,house) ) TYPE = ISAM; As you can see the field `house' is repeated in the `KEY' line Barry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
where would one put a .txt file to have mysql load it.? what dir. would it have to be in.? Richard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
At 19:00 -0500 2/15/03, Richard Nagle wrote: where would one put a .txt file to have mysql load it.? what dir. would it have to be in.? Anywhere you like, but the LOAD DATA statement must specify the pathname correctly so that MySQL can find it. Read the manual section on LOAD DATA, which lists the rules that indicate how MySQL interprets the pathname. Richard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error with libmyclient.a
[EMAIL PROTECTED] wrote: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query,queries,smallint If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Untung Rasidin wrote: You can download binary format (RPM) from What's RPM mean? I figured it's a compression and packaging format, but what are the initials? Any cousin to RTFM.. grin -- Amy and Joseph Kormann - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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.54 degrades and eventually hangs/has to be restarted
Reposted from my NG post, I'm hoping someone can help out, this is getting ugly. The problem I am describing is happenning in less than 24 hours now. As volume increases the period is getting shorter. I'm hoping someone can help me, I've got a problem with mysql going insane.. I've a quite busy application (avg 700 queries a second right now) - with about 40 odd seperate databases (scalability requirement) Anyway, depending on volume of transactions, it seems to go insane. I am using a mix of InnoDB for the important tables, and MyISAM for the raw data tables. After a while, one of my queries that uses RAND() as an order by simply stops returning any rows, either in the script that's calling it or the mysql shell. Remove the RAND() and it works fine. Then shortly after that, it fails altogether, and I have to kill a mysqld process that's hung and restart. This is on a FreeBSD 4.7 system, with 2gigs of ram. Restarting seems to fix it, so I have to assume that something's leaking. Can php cause mysql to leak resources? I dunno what's going on here, but the script is going to get substantially busier and I don't wanna have to be restarting this damn thing every few hours. Any help? I'll be quite happy to provide any information needed, but the system is currently running, so I can't be too crazy with it, I'd like to be able to diagnose and/or fix the problem without too much (or any) downtime. Cheers, Jason - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
More on Index's
Well I have leaned a lot on creating an index and then using it to
search - I do however get strange results sometimes in my names dbase
This is my index info
FULLTEXT KEY `surnamei`
(`SURNAME`,`GROOMSURNAME`,`BRIDESURNAME`,`OTHERSURNAME`,`MOTHERMNAME`)
) TYPE=MyISAM COMMENT='Surname Index'
My SQL is:
SQL = SELECT * FROM global WHERE MATCH (SURNAME, GROOMSURNAME,
BRIDESURNAME, OTHERSURNAME, MOTHERMNAME) AGAINST (' globsurname ')
Works well for most queries if I search on cohen it brings back rows
that don't contain cohen - how can that be, other names work fine
My index is fulltext is this the right type of index for an exact match
on a name , when I try to create a normal or unique Index I get the
error message specified key is to long: Max key length is 500
Regards
John Berman
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
