Problem: MySQL Client Mac OS X 10.2 (Jaguar: Build 6C106)
Hi, Is anybody else having a problem using the MySQL client with Mac OS X 10.2 (Jaguar: Build 6C106)? When I try to connect to the server on 'localhost' I get the following: dyld: ./bin/mysql Undefined symbols: ./bin/mysql undefined reference to _BC expected to be defined in /usr/lib/libSystem.B.dylib ./bin/mysql undefined reference to _PC expected to be defined in /usr/lib/libSystem.B.dylib ./bin/mysql undefined reference to _UP expected to be defined in /usr/lib/libSystem.B.dylib Trace/BPT trap If I then try to connect to the same server from another machine I get the following: ERROR 2003: Can't connect to MySQL server on server ip address (61) A socket problem of some sort??? The strange thing is I can connect using 'phpMyAdmin' without a problem from both machines. Just for the record I'm using binary of MySQL 3.23.51 obtained from http://www.entropy.ch. Any ideas? Cheers, Ian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ALTER TABLE and REFERENCES
Is there any plane to implement the REFERENCES function for foreign keys on the alter table command.or is it implemented? If it is what version of mysql? Chris Dion - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Accessing multiple indexes
It's my understanding that MySQL will only use one index per table on a given query. For example... SELECT * FROM HUGE_TABLE WHERE col1 = val1 AND col2 val2 AND col3 val3 ; If col1, col2, and col3 are indexed the query can only use one index, right? Single index access is a problem when you very large tables. What if you have a query with a result set of just 10 rows but there are no indexed columns that can limit the result set to 1 million rows? I really need to be able to use multiple indexes in a single table query and I don't want to have to perform self joins or create temp tables. Is there another way? Are there plans to overcome this limitation? TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: groupadd
Hello Thanks for getting back to. The real problem I have is that I am rtying to install mysql remotely and don't have access to the MacOSX console. Hence Netinfo is is not an option. On Thu, 25 Jul 2002, Chris Garaffa wrote: He who calles himself Gerald Clark (from [EMAIL PROTECTED]) wrote on 7/25/02 3:37 PM: create a group called mysql greate a user called mysql, and make it a member of the mysql group. And in case anyone's wondering, because it's not obvious in OS X, the best way to do so is to go to Applications - Utilities and load NetInfo Manager. Click the lock (bottom of the window) to make changes -- you must have an admin or the root password. In the Directory Browser, click /, then in the next pane, groups. The rightmost pane is the list of groups on your system currently. Duplicate an existing group (the icon with two folders on it). Change the name to mysql (double-click the name in the Value(s) column). **Change the group ID** and make sure it doesn't have the same value as another on the system (my mysql group is gid 251). Add user mysql. Click the disclosure triangle next to users, then go to the Directory menu, and choose New Value. You should probably delete the users in the group that were copied over... Next, you have to define the mysql user. Go to the column where you selected groups, and now select users. Duplicate one of the users (same procedure as for the group above). Edit the key/value (aka Property/Value(s)) pairs... Here's my values: PropertyValue(s) expire 0 realnameMySQL Database Server namemysql passwd * home/dev/null _writers_passwd mysql class Change 0 uid 251 shell /dev/null gid 251 Delete any extraneous properties. *Notes* Change the gid to the value you set for the group in the first set of steps. Make sure the shell is set to /dev/null so no one can telnet/ssh into your machine and use the mysql user... Make sure the uid is distinct... That is, that it is not the same as that for any other user on the system (same idea as the gid). Mine, for some reason, is set to the same id as my group. I guess that's safe as long as no other user is 251. Other than that, you should be good to go. Save confirm your changes, quit NetInfo Manager. Enjoy. (Did this on MacOS 10.1.5, with mysql already installed...) Also, sorry if I was too verbatim or lengthy. Just wanted to make everything clear, b/c OS X is still somewhat shady on the UNIX side of things (and that comment is *not* meant to start a flame war). -- Dave Houghton [EMAIL PROTECTED] System Manager http://finn.dlg.dmu.ac.uk/~djh Faculty of HumanitiesWork Tel. No. 0116 2506125 Clephan Building, Room 0.35 De Montfort University Leicester LE1 9BH - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Starting on Windows 2000 Server
At 09:42 25/7/2002 -0700, Wheeler, Andrew wrote: Hi, Hi, I just downloaded the binaries. I have followed the installation instructions. Have run the command mysqld-max-nt --install-manual and checked the service panel and MySql is installed as a service. In the same service panel screen right click the mysql service and select properties. Take a look at the first tab (General) if the path of the executable is correct e.g: C:\mysql\bin\mysqld-max-nt. If you find a wrong path, remove the service: mysqld-max-nt --remove and try to install again. Verify the path as above. Do you have a software which rename the hard drive letter in your Lan ? Regards, -- For technical support contracts, visit https//order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel Angel Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ São Paulo - Brazil ___/ 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: Calculating weeks
I use the Now() function, and I just set my format to ww without parenthesis and it gives me the current week number. --- Georg Richter [EMAIL PROTECTED] wrote: On Monday, 15. July 2002 20:28, Paul W. Reilly wrote: Hello Paul, maybe http://www.mysql.com/doc/D/a/Date_and_time_functions.html could answer your question (Function week() ) Regards Georg Trying to count weeks! I am doing a personal accounting system in php/mysql. I have a report section that groups and calculates expenses into running totals, so that I can see total amount spent in each category. I would like to add a break down to this that will show me the average weekly amount. The calculation is easy enough, but I can not figure out how to get the number of weeks being calculated. Any ideas would be greatly appreciated. Right now I am putting in the number of weeks in manually. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Starting on Windows 2000 Server
Hi, I just downloaded the binaries. I have followed the installation instructions. Have run the command mysqld-max-nt --install-manual and checked the service panel and MySql is installed as a service. However when I either try to start the service in the service panel or issue the command net start mysql I get the following error message Could not start the MySQL service in local computer. Error 3 The System cannot find the path specified. Per the documentation my.ini file is in the WINNT directory and reads: [mysqld] # set basedir to installation path, e.g., c:/mysql basedir=the_install_path # set datadir to location of data directory, # e.g., c:/mysql/data or d:/mydata/data datadir=the_data_path Change the values to the actual locations of your MySQL installation directory and data directory. Thanks for any help. Andrew Wheeler Phone 415-627-2054 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: Need help with Tables/Attributes
Hello Nitesh, You can find that and more at... http://www.mysql.com/doc/A/L/ALTER_TABLE.html Good Luck, --- Nitesh Divecha [EMAIL PROTECTED] wrote: Hello All, I need help with tables. I have created my table with some attributes in it. I need to modify my table attributes, can any one help me with the modify command or can give me the short cut to the web. Example: Mysql create table test(ID int(4), Name varchar(20), Sex varchar(1)); Now I need to change the attribute NAME TO Full name, HOW? Please help, Thanking in Advance. Regards Nitesh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Do you run multiple servers on WIndows?
I would, very much, like to know if anyone has been able to do this. NOTE: Multiple MySQL servers does not necessarily mean running multiple MySQL services (just to clarify to another reply on this) You can have multiple MySQL servers running on Unix system with some configuration in your my.cnf and using multi_mysqld instead of safe_mysqld http://www.mysql.com/doc/m/y/mysqld_multi.html As far as I can tell, the windows version does not have a multi_mysqld and I can find no other configuration choices to do it. The first line of the above page reads mysqld_multi is meant for managing several mysqld processes running in different Unix sockets and TCP/IP ports. If there is no ability for the windows version to do this, then here is my vote to get that done some time -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 25, 2002 12:35 PM To: [EMAIL PROTECTED] Subject: Do you run multiple servers on WIndows? It's not uncommon to run multiple MySQL servers on a given Unix system. I'm wondering: Does anyone do this on Windows, and if so, what particular configuration issues did you have to solve to keep them from interfering with each other? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: FIND_IN_SET index problem
Hi.
On Thu 2002-07-25 at 10:49:47 -0500, [EMAIL PROTECTED] wrote:
Hi, I have a problem where the index for a SET column does not get used
once I link in another table.
The output to explain:
mysql explain select count(*) from NS_articles where
find_in_set('approved', artFlags) 0;
+-+---+---+--+-+--+--+-+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+-+---+---+--+-+--+--+-+
| NS_articles | index | NULL | artFlags | 2 | NULL | 560
| where used; Using index |
+-+---+---+--+-+--+--+-+
This is good, the index on artFlags is being used. Now I link in another
table:
No, the index is not really used (possible_keys = NULL), because you
have an expression (FIND_IN_SET()) instead of a column and MySQL
cannot indexes on (most/any?) expressions. I.e. it is not used to find
the rows based on an index lookup, but MySQL sees that you are only
interested in column(s) from an index and therefore reads in the
index, instead of the data file (using index). Well, in hindsight,
using index surely was not best choice to describe that behaviour.
To make that more clear. Imagine you have 1.000.000 rows, 100 bytes
each. An index on the column of interest needs 10 bytes per row.
Looking up 200 rows of a range without index, reads the whole table
(i.e. the data file): about 1.000.000 * 100 bytes = ~100MB.
Reading them using only the index file (using index) reads 1/10th of
that (10 bytes instead of 100 bytes per row), i.e. 10MB and one can
assume that this will also be faster about 10 times (this is specific
to this example, of course). That is why MySQL does this in your case.
Really making use of the index for the lookup, needs about 1 index
lookup for the first row, reading log2(1.000.000) =~ 20 index pages
(each 1KB usually) plus the 199 remaining rows, each 100 bytes, or 19
index pages (10 fitting in one 1KB page; in reality, it's less, let's
say 5). I did not consider the data file intentionally (using index
additionally, you know). So it reads less than 60KB, but needs
additional 20 disk seeks. That would be real good! ;-)
mysql explain select count(*) searchTotal from NS_articles nsa,
NS_editors nse where find_in_set('approved', nsa.artFlags) 0 and
nsa.artEditor = nse.id;
+---+---+---+---+-++--+-+
| table | type | possible_keys | key | key_len | ref| rows |
Extra |
+---+---+---+---+-++--+-+
| nse | index | PRIMARY | PRIMARY | 32 | NULL | 21 |
Using index |
| nsa | ref | artEditor | artEditor | 32 | nse.id | 19 |
where used |
+---+---+---+---+-++--+-+
What happened to the index on artFlags? The table link uses indexes
which is good, but now artFlags is doing a raw table scan.
I presume it is because you do not have any key in NS_articles which
covers both, id and artFlags. I.e. for using index you would need a
combined key (id, artFlags).
But as explained above, you should get the real speed boost if you can
somehow manage to avoid the expression instead of only the column
name. Unfortuneately, that is not possible in the common case. If you
query very often based on this 'approved' flag, you may want to
consider to give it an own column (which should use indexes
appropriately).
For more details, see the manual sections about index usage and
optimization.
Greetings,
Benjamin.
--
[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: Query problem
Hi, Use LEFT JOIN instead of =. In a 1:m relationship, only records that match in both tables are selected. With L.J., the master is always selected even if the record is not present in the child. Master Id 1 2 3 4 5 Children FK_id Value 1 V1 1 V2 2 V3 Select id, value from Master, Children Where Master.Id = Children.FK_id 1, V1 1, V2 2, V3 Select id, value from Master Left Join Children On Master.Id = Children.FK_id 1, V1 1, V2 2, V3 4, NULL 5, NULL This query is very useful to find records without children Select id, value from Master Left Join Children On Master.Id = Children.FK_id Where value is null Bye and Good Luck. --- Kevin [EMAIL PROTECTED] wrote: Hello, I have a query that runs perfectly until one of the items has no value or is set to 0: SELECT item.*, color.Name AS COLOR, shapecode.Shape AS SHAPE, clarity.Name AS CLARITY FROM item, color, shapecode, clarity WHERE clarity.ID = item.CLARITY_ID AND shapecode.ID = item.SHAPE_ID AND item.COLOR_ID = color.ID AND ITEM_ID='MA603' If the item.CLARITY_ID has no value or a value of 0 (which there is no defined value for in the clarity table), then the query fails. Can anything be done without adding more login in the code? Thanks --Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Change string quote char
On Fri, 26 Jul 2002, Benjamin Pflugmann wrote:
is it possible to change the default string quoting chars (')?
I'm using MySQL with an older shop solution. The data to write has
both: ' and chararcters. It isn't possible to escape those
chars. Therefor I have to change the quoting char to a char
that isn't used i.e #.
Is it possible?
Not by any normal mean. Only by changing the source and recompiling
the MySQL server yourself.
I am irritated. Why isn't it possible to change the escaping, but to
change the quoting chars in the shop or the layer doing the SQL
queries?
Hm. You can change the quoting delimiters if you do not use SQL, but
use LOAD DATA INFILE to read in a CSV delimited file.
It your program is SQL-compliant, as you indicate it as old, you might be
able to get to use some of the lesser known ANSI SQL syntax tricks that do
work in MySQL.
Eg the following query:
SELECT 'FOO''BAR';
This will return FOO'BAR on nearly every database system in existance, as
it has been around in all of the ANSI SQL declarations.
Your shop program should support that at the very least.
--
Robin Hugh Johnson
E-Mail : [EMAIL PROTECTED]
Home Page : http://www.orbis-terrarum.net/?l=people.robbat2
ICQ# : 30269588 or 41961639
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Delay in SELECT with innodb
Hi, We have a problem : We are working with MySql ver 3.23.51 in Windows NT 4.0 Service Pack 6. We use Acctualy MyIsam. It works fine, SELECT works fast with any indexes ( primary and others ) But when try to use InnoDB, only the primary key works like MyIsam With another keys, the delay is the same that we work without index. this is my.ini file about InnoDB innodb_data_file_path = ibdata1:1000M:autoextend innodb_data_home_dir=d:\mysql\data\prueba set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = d:\mysql\data\iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=30M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = d:\mysql\data\iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 #skip-locking #set-variable = max_connections=16M #set-variable = record_buffer=1M #set-variable = sort_buffer=1M #set-variable = key_buffer=16M Can somebody help us, or tell us where we'll can solve this problem ? Thank you very much --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.380 / Virus Database: 213 - Release Date: 24/07/02 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: groupadd
On Thu, 25 Jul 2002, Gerald Clark wrote: create a group called mysql greate a user called mysql, and make it a member of the mysql group. There is no groupadd or useradd command on MacOSX. -- Dave Houghton [EMAIL PROTECTED] System Manager http://finn.dlg.dmu.ac.uk/~djh Faculty of HumanitiesWork Tel. No. 0116 2506125 Clephan Building, Room 0.35 De Montfort University Leicester LE1 9BH - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Starting on Windows 2000 Server
Andrew, I assume that basedir and datadir are set to the *actual* paths specific to your install and not 'the_install_path' and 'the _data_path'. If not, you will need to set those. Bhavin. - Original Message - From: Wheeler, Andrew [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 25, 2002 9:42 AM Subject: Starting on Windows 2000 Server Hi, I just downloaded the binaries. I have followed the installation instructions. Have run the command mysqld-max-nt --install-manual and checked the service panel and MySql is installed as a service. However when I either try to start the service in the service panel or issue the command net start mysql I get the following error message Could not start the MySQL service in local computer. Error 3 The System cannot find the path specified. Per the documentation my.ini file is in the WINNT directory and reads: [mysqld] # set basedir to installation path, e.g., c:/mysql basedir=the_install_path # set datadir to location of data directory, # e.g., c:/mysql/data or d:/mydata/data datadir=the_data_path Thanks for any help. Andrew Wheeler Phone 415-627-2054 email [EMAIL PROTECTED] _ IMPORTANT NOTICES: This message is intended only for the addressee. Please notify the sender by e-mail if you are not the intended recipient. If you are not the intended recipient, you may not copy, disclose, or distribute this message or its contents to any other person and any such actions may be unlawful. Banc of America Securities LLC(BAS) does not accept time sensitive, action-oriented messages or transaction orders, including orders to purchase or sell securities, via e-mail. BAS reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the BAS e-mail system. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Need help with Tables/Attributes
Hello All, I need help with tables. I have created my table with some attributes in it. I need to modify my table attributes, can any one help me with the modify command or can give me the short cut to the web. Example: Mysql create table test(ID int(4), Name varchar(20), Sex varchar(1)); Now I need to change the attribute NAME TO Full name, HOW? Please help, Thanking in Advance. Regards Nitesh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Do you run multiple servers on WIndows?
From what little I know of MySql on Windows, it runs as a service and you can't run multiple services on the same Win Server. Bhavin. - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 25, 2002 9:35 AM Subject: Do you run multiple servers on WIndows? It's not uncommon to run multiple MySQL servers on a given Unix system. I'm wondering: Does anyone do this on Windows, and if so, what particular configuration issues did you have to solve to keep them from interfering with each other? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Change string quote char
Hi.
On Thu 2002-07-25 at 18:28:20 +0200, [EMAIL PROTECTED] wrote:
Hi,
is it possible to change the default string quoting chars (')?
I'm using MySQL with an older shop solution. The data to write has
both: ' and chararcters. It isn't possible to escape those
chars. Therefor I have to change the quoting char to a char
that isn't used i.e #.
Is it possible?
Not by any normal mean. Only by changing the source and recompiling
the MySQL server yourself.
I am irritated. Why isn't it possible to change the escaping, but to
change the quoting chars in the shop or the layer doing the SQL
queries?
Hm. You can change the quoting delimiters if you do not use SQL, but
use LOAD DATA INFILE to read in a CSV delimited file.
Greetings,
Benjamin.
--
[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
Error updating table structure.
I am developing a new application using MySQL instead of MS SQL Server 2000, I have just shelled out for the Microolap Delphi SQL DIrect Access Components, having successfully converted an existing SQL Server database to MySQL. HOWEVER, I have tried using MyCC, MySQL Explorer, and PremiumSoft MySQL Studio to add a new field to a simple table, and I keep getting an error message like [localhost] ERROR 7: Error on rename of '.\rocktest\companies.MYI' to '.\rocktest\#sql2-94c-21.MYI' (Errcode: 13) If I save to a new table it works, but of course the data is not copied. I am running Windows XP Professional, MySQL 4.0.1-alpha-nt. Anyone know what's wrong? What do other people use to edit table structures (and don't say SQL queries because that's not a productive option when compared to MS Access linked to SQL Server. Regards Jeff Dyer Logic Software Design www.logicsoftware.co.uk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Do you run multiple servers on WIndows?
clarification: can't run multiple instances of the same service on Windows. - Original Message - From: Bhavin Vyas [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, July 25, 2002 7:07 PM Subject: Re: Do you run multiple servers on WIndows? From what little I know of MySql on Windows, it runs as a service and you can't run multiple services on the same Win Server. Bhavin. - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 25, 2002 9:35 AM Subject: Do you run multiple servers on WIndows? It's not uncommon to run multiple MySQL servers on a given Unix system. I'm wondering: Does anyone do this on Windows, and if so, what particular configuration issues did you have to solve to keep them from interfering with each other? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: RE: Problem downloading MySQL4.0.2a for windows
Hi Victoria, Thanks. Does this mean in the future the -max version would be merged into one release or just for the 4.0.2a?? Many thanks. Regards, Michael -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 25, 2002 1:07 AM To: [EMAIL PROTECTED] Subject: Re: RE: Problem downloading MySQL4.0.2a for windows Tam, Tuesday, July 23, 2002, 9:38:17 PM, you wrote: T Hi Victoria, TDo you know when the 4.0.2a-MAX would be available for download then? T Thank you. Use mysql-win-4.0.2-alpha.zip instead. It the same as with -max. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Do you run multiple servers on WIndows?
Hi! You can run multiple servers on any Windows version with any MySQL version. They must NOT share any data, .frm, or log files. Below Miguel explains how with MySQL-4.0.2 you can run multiple instances also as Windows services. For any MySQL version you can do like this: You have to create a separate my.cnf file for each instance, configure a different port number for each instance, and start each mysqld instance from an MS-DOS prompt like this: mysqld --defaults-file=your_path_to_the_my_cnf_of_this_instance --console You can use the mysql.exe client to query each instance like this: mysql --defaults-file=your_path_to_the_my_cnf_of_the_instance Below is the relevant portion of the my.cnf file of a slave mysqld in my own Windows computer. The master uses the default port number of mysqld, 3306. I have set the slave port number to a different value, 3308. [mysqld] port=3308 master-host=omnibook master-user=slaveuser master-password=slaveuser master-port=3306 server-id=2 basedir=c:/slave datadir=c:/slave/data innodb_data_home_dir=e:/ibdataslave innodb_data_file_path=ibdata4:10M:autoextend innodb_log_group_home_dir=c:/slave/data innodb_log_arch_dir=c:/slave/data Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Copied message from Miguel: ... At 19:07 25/7/2002 -0700, Bhavin Vyas wrote: Hi, From what little I know of MySql on Windows, it runs as a service and you can't run multiple services on the same Win Server. cut From 4.0.2 release you are able to run several services. For example: For to run two services: the first one using mysqld-nt.exe and the second one using the mysqld-max-nt.exe. Installing mysqld-nt.exe command at the prompt: mysqld-nt --install or mysqld-nt --install-manual Installing mysqld-max-nt.exe command at the prompt: mysqld-max-nt --install mysqldopt or mysqld-max-nt --install-manual mysqldopt Now you have the default service called mysql for to run mysqld-nt.exe and the service mysqldopt for to run mysqld-max-nt.exe. Now you edit the /winnt/my.ini file: [mysqld] #for to set the server mysqld-nt.exe port=3306 basedir=c:/mysql datadir=c:/mysql/data . [mysqldopt] #for to set the server mysqld-max-nt.exe the same name of #the service port=3307 basedir=c:/mysql datadir=d:/mysqldopt/data skip-innodb skip-bdb . Notes: 1- You can use the same basedir but not the same executable. 2- You must to have a different datadir (the whole directory) for each server. You can't to mix the mysql database and the working databases. 3- You need to have a different port. In this way for example you can use one server as master and the other as slave on the same machine; of course both handling the same type of tables. In the example above only MyISAM tables. Regards, -- For technical support contracts, visit https//order.mysql.com / |/ // /\/ /Miguel Angel Solórzano [EMAIL PROTECTED] / /| / / // /\ \/ / / / / MySQL AB, Fulltime Developer / / / /\ , / /\ \ \ / São Paulo - Brazil / www.mysql.com - Original Message - From: Bhavin Vyas [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, July 26, 2002 8:38 AM Subject: Re: Do you run multiple servers on WIndows? clarification: can't run multiple instances of the same service on Windows. - Original Message - From: Bhavin Vyas [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, July 25, 2002 7:07 PM Subject: Re: Do you run multiple servers on WIndows? From what little I know of MySql on Windows, it runs as a service and you can't run multiple services on the same Win Server. Bhavin. - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 25, 2002 9:35 AM Subject: Do you run multiple servers on WIndows? It's not uncommon to run multiple MySQL servers on a given Unix system. I'm wondering: Does anyone do this on Windows, and if so, what particular configuration issues did you have to solve to keep them from interfering with each other? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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]
Re: Alter table and sort_buffer_size is to small
MP But when I do it I get: MP ERROR 1034: sort_buffer_size is to small Hm.. Error 1034 means: Incorrect key file for table ... not a sort_buffer_size is to small How did you installed the server? MP I compiled it from source files. Something is very wrong with your installation. Can you install MySQL from binary distribution from www.mysql.com and check if error occurs again? HI, I can't really do this because it's production machine and I can't change the MySQL instalation. Do you think it will help if I try the latest 3.23.51? But I'll have to compile it from the source again. -- Martin Pavlas Pwrgeneration.net ICC s.r.o. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 sub query
Hello All I have the following situation ID GRP PRC CHECK 1 3 10.0Y 2 2 12.0Y 3 3 32.0N 4 1 5.0 N 5 1 7.0 Y 6 2 9.0 N 7 3 17.0N 8 1 11.0N Problem is that the sql version I am running does not support sub-queries. Is there another way of getting around the following situation to wirte a query so that select * from tab_name where one item from each GRP 1, 2, and 3 - whichever is highest priced (PRC) and has CHECK set to N. Thanks for the help Aman _ Send and receive Hotmail on your mobile device: http://mobile.msn.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: max_connections recovery
The language is English (same as server) and the client is PHP. I've never seen this problem even when maxing out the connections at the default 100. On Thursday, July 25, 2002, at 02:55 PM, Georg Richter wrote: On Thursday, 25. July 2002 18:48, Troy Hakala wrote: Hello, I've been running MySQL for 2 years with no problems. Yesterday, I increased the max_connections because I was getting too many connections error. This morning, the server again reached its limit but it never seemed to recover from it and constantly gave the too many connections error. I was forced to kill restart the server. does this sound familiar? this is version 3.23.44, btw. which language/program do you use at the client side? Mostly this is not a server problem it's a client problem. Regards Georg -- Troy Hakala Recipezaar.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: Query problem
Hi, On Thu, 2002-07-25 at 20:40, Kevin wrote: Hello, I have a query that runs perfectly until one of the items has no value or is set to 0: SELECT item.*, color.Name AS COLOR, shapecode.Shape AS SHAPE, clarity.Name AS CLARITY FROM item, color, shapecode, clarity WHERE clarity.ID = item.CLARITY_ID AND shapecode.ID = item.SHAPE_ID AND item.COLOR_ID = color.ID AND ITEM_ID='MA603' If the item.CLARITY_ID has no value or a value of 0 (which there is no defined value for in the clarity table), then the query fails. Can anything be done without adding more login in the code? Hi, I don't know if CLARITY_ID can be NUll, so this will be just a guess. Try to use the NULL safe equal operator '=' between clarity.ID and item.CLARITY_ID like ... WHERE clarity.ID = item.CLARITY_ID ... Info: 6.3.1.2 Comparison Operators http://www.mysql.com/doc/C/o/Comparison_Operators.html --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Tables/Attributes
Hi, I suppose that what you want would be something like: Mysql create table test(ID int(4), Name varchar(20), Sex varchar(1)); Mysql alter table test change Name FullName varchar(40); You should read this for more information about ALTER TABLE: http://www.mysql.com/doc/A/L/ALTER_TABLE.html On Thu, 2002-07-25 at 23:45, Nitesh Divecha wrote: Hello All, I need help with tables. I have created my table with some attributes in it. I need to modify my table attributes, can any one help me with the modify command or can give me the short cut to the web. Example: Mysql create table test(ID int(4), Name varchar(20), Sex varchar(1)); Now I need to change the attribute NAME TO Full name, HOW? Please help, Thanking in Advance. Regards Nitesh -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Fw: Complex SQL assistance
On Fri, 2002-07-26 at 05:17, Corey Wallis wrote: Peoples, I'm currently trying to work out if this is possible by SQL. I have the need to use SQL and only SQL to achieve the following. If a record exists and meets a certain criteria (i.e. field X = '1234') then update the record. If the record doesn't exist then insert it. See if REPLACE does what you want: http://www.mysql.com/doc/R/E/REPLACE.html --Dan Koch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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]: TRUNCATE TABLE
Hello Benjamin, Why after TRUNCATE TABLE data file with indexes not freed ? BP See http://www.mysql.com/doc/A/d/Adding_and_removing.html (2nd paragraph) BP and http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#InnoDB_File_space BP (second link does not work in the searchable doc, whyever) and TRUNCATE TABLE is very slow ... BP See http://www.mysql.com/doc/T/R/TRUNCATE.html BP and http://www.mysql.com/doc/I/n/InnoDB_restrictions.html BP (in short: because it's InnoDB on 3.23, it's deleting all rows BP seperately) All my tables has MyISAM type, but server running with InnoDB support. MySQL 3.23.51+InnoDB OS: Linux 2.4.18/ReiserFS Best regards, Andrew Sitnikov e-mail : [EMAIL PROTECTED] GSM: (+372) 56491109 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fwd: Re: Question about group by or distinct
Hi Benjamin, Arjen I did some testing again. I have 75,000,000 rows table, after using group by I got 62,300,000 rows. If I use *nix -u sort, I got 74800,00 rows. I compared the two results, I feel I trust my *nix result. Some lines just miss in group by' result, look like no reason at all. I think this is a bug. If you like to see my two result, I can attach to you. Thanks. Arjen, I don't have any NULL field in my table, but I have empty field which is ' ' in my some fields. Is that reason the different between SELECT DISTINCT and *nix sort? If the answer is yes, I have to dump the table out then use the *nix sort. Thanks At 01:05 PM 7/24/2002 +1000, you wrote: Hi Benjamin, Vivian, On Wed, 2002-07-24 at 05:23, Benjamin Pflugmann wrote: On Tue 2002-07-23 at 14:58:16 -0400, [EMAIL PROTECTED] wrote: I have 14 fields are chars (like char(8), char(9)), and one field is int(4). All char fields are mixed with upper case letters, numbers(0-9) and sign(like $). Because there is no low case char, I don't have to put -f option for unix sort. Well, it never hurts to try anyhow... *hint* Does the query use indexes? If so, please check they are not corrupted (e.g. using CHECK TABLE). Which character set is MySQL set to, which locale is set in your shell? Maybe the collections are different and consider different characters the same. Please try to find some rows which disappear. For example, dump the result from the query (62,000,000 rows) to a file like you have done for the 76,500,000 rows and find out which rows got removed (e.g. sorting the files and using comm on the 76,000,000 and 62,000,000 version). In short, try to make a reproducable test case. Thanks. Note that SELECT DISTINCT only grabs rows where none of the distinct columns is NULL. A *nix sort on a dump would not do the same. Try doing a grep -v NULL first before sorting. Then see what you have left. However, if your table dump also contains NULLs in fields that are not part of the DISINCT selection, then you'll need to make the filtering slightly more complicated to have it produce an equivalent result. Regards, Arjen. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible bug?
Looks as if the MySQL DB crashed and recovered. Linux 2.4.18-5smp #1 SMP MySQL version 3.23.51 From the MySQL server while running mtop 26JUL02 ~6:45AM PST: Unable to execute show procs [Lost connection to MySQL server during query] Stack Trace: at main::__ANON__(/usr/local/bin/mtop:446) at main(/usr/local/bin/mtop:943) From the error log (/data/mysql/var/*.err): mysqld got signal 11; Stack trace: 0x80b980e 0x40027f75 0x400290c6 0x400261cc 0x81146cb 0x80c19e1 0x80c3117 0x80bf55a 0x80bea14 Built symbol file: nm -n /data/mysql/libexec/mysqld /tmp/mysqld.sym Created stack file: /data/mysql/var/errors/mysqld.stack.200207260645 Tried to find reason for crash: /data/mysql/bin/resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack Got: 0x80b980e handle_segfault__Fi + 406 0x40027f75 _end + 937971053 0x400290c6 _end + 937975486 0x400261cc _end + 937963460 0x81146cb mysqld_list_processes__FP3THDPCcb + 1915 0x80c19e1 mysql_execute_command__Fv + 6805 0x80c3117 mysql_parse__FP3THDPcUi + 211 0x80bf55a do_command__FP3THD + 1374 0x80bea14 handle_one_connection__FPv + 592 Searched G for handle_segfault: Only results for Mac OS and errors on compilation of MySQL Searched G for mysqld_list_processes: The above happens if a new user logs in at the same time you do mysql_list_processes(). This is fixed in the newest MySQL 3.22 version! --We are using version 3.23.51 - shouldn't be a problem? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Fw: Complex SQL assistance
SQL does not exist in a vacuum. You have to run a client. The client is part of or runs under some scripting language ( perl , php, sh, command.com ) which in turn runs on an operating system. You have to somehow pass values that you want to select, update, or insert. So it really not possible to run JUST SQL. What are you using? Corey Wallis wrote: Peoples, I'm currently trying to work out if this is possible by SQL. I have the need to use SQL and only SQL to achieve the following. If a record exists and meets a certain criteria (i.e. field X = '1234') then update the record. If the record doesn't exist then insert it. For reasons too complex to go into at this stage using anything except SQL is not possible. Any and all suggestions welcome. -Corey - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Fw: Complex SQL assistance
SQL does not exist in a vacuum. You have to run a client. The client is part of or runs under some scripting language ( perl , php, sh, command.com ) which in turn runs on an operating system. You have to somehow pass values that you want to select, update, or insert. So it really not possible to run JUST SQL. What are you using? mysql foo.sql Our nightly report generation script is a 400 line .sql file. Our hourly report generation script is a 280 line .sql file. No scripting language using some DB API to feed queries to the server -- just mysql whatever.sql. What one can accomplish with this approach is quite limited by the fact that MySQL's SQL implementation doesn't rise to the level of full programming language unlike, for example, Oracle's PL/SQL in which it is quite possible to write loops, conditional logic, etc. Now, to address the original question: If field X is a unique field (PRIMARY KEY or UNIQUE INDEX) you could try this: INSERT IGNORE INTO whatever SET x = '1234', ...; UPDATE whatever SET ... WHERE x = '1234'; Under ideal circumstances this results in a bit of redundant DB access, which may or may not matter to you depending on your circumstances. If there is no uniqueness constraint on field x, then this technique will not work and it's unlikely that just SQL (MySQL's SQL anyway) will be adequate. The TODO list for MySQL contains exactly this item though (update a row if it exists, otherwise insert it, aka REPLACE INTO behavior for UPDATEs) but no estimate as to when it will be included. -JF Corey Wallis wrote: Peoples, I'm currently trying to work out if this is possible by SQL. I have the need to use SQL and only SQL to achieve the following. If a record exists and meets a certain criteria (i.e. field X = '1234') then update the record. If the record doesn't exist then insert it. For reasons too complex to go into at this stage using anything except SQL is not possible. Any and all suggestions welcome. -Corey - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fw: Complex SQL assistance
Take a look at REPLACE: http://www.mysql.com/doc/R/E/REPLACE.html On Fri, 2002-07-26 at 10:17, Corey Wallis wrote: Peoples, I'm currently trying to work out if this is possible by SQL. I have the need to use SQL and only SQL to achieve the following. If a record exists and meets a certain criteria (i.e. field X = '1234') then update the record. If the record doesn't exist then insert it. For reasons too complex to go into at this stage using anything except SQL is not possible. Any and all suggestions welcome. -Corey -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with win98
Pada Thu, 25 Jul 2002 11:44:26 -0400 [EMAIL PROTECTED] menulis : Mysql engine is running under win98, then at the prompt I type mysql mysql then press enter and I have control. I can delete users (root included), whatever I want, so How can I make Mysql always require a username and a password to connect to it? make sure your mysqld is running ;-) run ms dos prompt go to mysql bin directory run : mysqladmin -uroot password your_new_password_for_root_user after that, you have to use user root and the password to login, don't forget to create new users if necessary. to login with password : mysql -uusername -p -- Let's call it an accidental feature. -- Larry Wall MySQL 3.23.51 : up 34 days, Queries : 355.101 per second (avg). -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP : Jl Kapt. Tendean No. 34 - Jakarta Selatan (12790) Phone : +62 21 79199577 - Web : http://1rstwap.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: Do you run multiple servers on WIndows?
Hi, When starting the MySQL server from the command line, you can successfully run multiple MySQL servers in the same Windows machine. This is possible because you can specify the port number MySQL has to bind to on the command line. All you have to do is run each server on a different port. I'm not sure if and don't think it is possible to tell MySQL which network interface to use, in case the machine has more than one. However running MySQL as a service, the only way to specify the port number is by using the my.cnf configuration file. Although you can set up multiple MySQL services, they will all read the same config file and you will not be able to run each on a different port. HTH Kind Regards Antonie, SciBit MySQL Team -Original Message- From: Paul DuBois [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] CC: Subject: Do you run multiple servers on WIndows? Sent: Thu, 25 Jul 2002 16:35:21 GMT Received: Thu, 25 Jul 2002 21:02:31 GMT Read: Sat, 30 Dec 1899 00:00:00 GMT It's not uncommon to run multiple MySQL servers on a given Unix system. I'm wondering: Does anyone do this on Windows, and if so, what particular configuration issues did you have to solve to keep them from interfering with each other? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Re: IBM-AIX 4.3 MySQL Binnary Installation Problem: libz ismissing.
Francisco writes: Also, when I try to compile mysql-3.23.51.tar.gz, I get the following error executing the make command: my_gethostbyname.c, line 81.5: 1506-068 (S) Operation between types struct hostent* and int is not allowed. You need to modify the config file, since you are using the VisualAge compiler, to change the #define for the GETHOST stuff : /* In OSF 4.0f the 3'd argument to gethostname_r is hostent_data * */ #define HAVE_GETHOSTBYNAME_R_RETURN_INT 1 more than likely, this is commented out in your version of config.h You should also read the following : http://groups.google.com/groups?hl=enselm=9s779b%24o9a%241%40FreeBSD.csie. NCTU.edu.twrnum=4 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HANDLER
Does anybody have any PHP examples of how to use the mySQL HANDLER command in place of a select statement? From the manual it appears as a high speed version of SELECT. Cheers, 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: Re: Question about group by or distinct
Hi. On Fri 2002-07-26 at 11:00:31 -0400, [EMAIL PROTECTED] wrote: Hi Benjamin, Arjen I did some testing again. I have 75,000,000 rows table, after using group by I got 62,300,000 rows. If I use *nix -u sort, I got 74800,00 rows. I compared the two results, I feel I trust my *nix result. Some lines just miss in group by' result, look like no reason at all. I think this is a bug. If you like to see my two result, I can attach to you. Well, my answer to this you can find in my former mail, already: [...] Please try to find some rows which disappear. For example, dump the result from the query (62,000,000 rows) to a file like you have done for the 76,500,000 rows and find out which rows got removed (e.g. sorting the files and using comm on the 76,000,000 and 62,000,000 version). Greetings, Benjamin. PS: No, I do not think, that I would like a dump 75,000,000 rows send to me. ;-) -- [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: HANDLER
At 16:33 +0100 7/26/02, Steve Howie wrote: Does anybody have any PHP examples of how to use the mySQL HANDLER command in place of a select statement? From the manual it appears as a high speed version of SELECT. Use HANDLER ... OPEN, then treat HANDLER ... READ like a SELECT, then use HANDLER ... CLOSE. Cheers, 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
Querying MYSQL and Return Variable Length
I'm using coldfusion to update a MySQL Database. on my edit record page I'm querying the Database for some fields, i get most of the fields but one field (Field A let's say) only returns 40 chars or so when it is a blob field set to 65000 chars, were not actually using that many chars but it's nice to have and performance is not a big issue. If anyone can help me out it would be much appreciated, I'm not sure if it's a MySQL thing or a cold fusion thing. Server Details- MySQL 3.23.51-max ODBC- MySQL ODBC 3.51 ColdFusion- 4.5 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Named Pipes in MySQL
At 09:16 26/7/2002 -0500, Mike Grover wrote: Hi, cut Does anybody know if connecting with named pipes has changed in 4.02? I used to use . For the host name in 4.01 , after I switched to 4.02 I get the error Failed to open named pipe when I try to connect? Any Ideas? Yes, on 4.0.2 the named pipe are disabled by the default. For to enable it you should use the set enable-named-pipe: Microsoft Windows 2000 [Versão 5.00.2195] (C) Copyright 1985-1999 Microsoft Corp. C:\cd\mysql\bin C:\mysql\binmysqld-max-nt --console --standalone --enable-named-pipe mysqld-max-nt: ready for connections C:\mysql\binmysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.0.2-alpha-max-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show variables like n%; +---+---+ | Variable_name | Value | +---+---+ | named_pipe| ON| | net_buffer_length | 7168 | | net_read_timeout | 30| | net_retry_count | 10| | net_write_timeout | 60| +---+---+ 5 rows in set (0.00 sec) Regards, -- For technical support contracts, visit https//order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel Angel Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ São Paulo - Brazil ___/ 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: Facing some problem in installation
Manisha wrote: Hi, I am not a root. Our server is at ISP and I am trying to install mysql on our shared server. They have provided one user id and password with which I can access it. I also do not know whether mysql previously exists or not as mysql.sock is existing. (how to find that ?) I checked the mysql.sock file permissions it is as follows: srwxrwxrwx 1 mysql wheel .mysql.sock and as it is a special file, I am unable to delete it. shell rm mysql.sock command gives - permission denied Actually I tried to vi mysql.sock - but ended with something else only now how to proceed ? What shall I check ? Ah, oki, if you are not root, it is ok, just change the socket= /var/run/mysqld/mysqld.sock pid-file= /var/run/mysqld/mysqld.pid to places where you got write permissions (in your my.cnf file). I'm not very sure, but maybe you must recompile your source with a parameter that sets the config directory too (else it tries to look somewhere in /etc, where you surely got no permissions), maybe it is best to set --prefix=/usr/home/mysql which might be sufficient (it is, if after compiling there is a directory where my.cnf resides) Greetings Ralf sql query -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 sub query
On Fri, 2002-07-26 at 15:46, Aman Raheja wrote: I have the following situation IDGRP PRC CHECK 1 3 10.0Y 2 2 12.0Y 3 3 32.0N 4 1 5.0 N 5 1 7.0 Y 6 2 9.0 N 7 3 17.0N 8 1 11.0N Problem is that the sql version I am running does not support sub-queries. Is there another way of getting around the following situation to wirte a query so that select * from tab_name where one item from each GRP 1, 2, and 3 - whichever is highest priced (PRC) and has CHECK set to N. Hi, Try this: SELECT ID,GRP,max(PRC) FROM tab_name WHERE GRP IN (1,2,3) and CHECK='N' GROUP BY GRP; Hope this helps, -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SUMMARY how to retrieve data from two tables?
First thanx to all those how answered: Benjamuin Pflugmann Gerald Clark. hcir Dicky Purnomo Original posting is below. The best answer came from Benjamin and he also gave a good explanation why: Because it relates to the times table and you want to see the users regardless, you have to put it in the ON clause, too: SELECT u.id, u.surname, t.* FROM users u LEFT OUTER JOIN times t ON u.id = t.id AND t.date = [current_date] WHERE u.id = 22 In other words, whenever you can say I want to see the content of the left table (here: user) even if there is no match in the right one (here: times), you have to put the condition in question into the ON clause of the LEFT JOIN instead of the WHERE clause (which would restrict the end result). I got this from my tables: 22|Price|NULL|NULL| NULL|NULL|NULL|NULL|NULL| NULL|NULL | Which is exactly what I want - I can use the surname,id to reply to the user. I also know that they haven't used the system that day - which is why, for those that asked, you don't see date information, because that user hasn't got an entry for that day. Anyway I mighty thanx to Benjamin for that cos I learnt something today. Dp. - Original posting - I want to select data from 2 tables conditionally. 1 table has users, the other times relating to the users. I want to create a query that will select the user from the users table and if they have any time data against them (for that day) return that also. If there is not data for that day only the user data should be returned. If I do: SELECT u.id,u.surname,t.* FROM users u LEFT OUTER JOIN times t ON (u.id =t.id) WHERE u.id = 22; I get: 22|Price|NULL|NULL| NULL| NULL| NULL| NULL| NULL| NULL Which I could work with but once I add AND t.date = (current_date) I get 0 records returned and I need the users.id an users.surname. Can anyone think of a way round this? ~~ Dermot Paikkos * [EMAIL PROTECTED] Network Administrator @ Science Photo Library Phone: 0207 432 1100 * Fax: 0207 286 8668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Logs
Hello All, I need help with MySQL Log, can I know where does MySQL saves the log files. I need a log for ODBC; I have some problem with ODBC connection from outside. I need to know what the Error is. Please advice, Thanking in Advance. Regards Nitesh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: Complex SQL assistance
Peoples, I'm currently trying to work out if this is possible by SQL. I have the need to use SQL and only SQL to achieve the following. If a record exists and meets a certain criteria (i.e. field X = '1234') then update the record. If the record doesn't exist then insert it. For reasons too complex to go into at this stage using anything except SQL is not possible. Any and all suggestions welcome. -Corey - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Tables/Attributes
Nitesh, Friday, July 26, 2002, 1:45:35 AM, you wrote: ND I need help with tables. I have created my table with some attributes in ND it. ND I need to modify my table attributes, can any one help me with the ND modify command or can give me the short cut to the web. ND Example: ND mysql create table test(ID int(4), Name varchar(20), Sex varchar(1)); ND Now I need to change the attribute NAME TO Full name, HOW? Use ALTER TABLE: http://www.mysql.com/doc/A/L/ALTER_TABLE.html -- 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: Query problem
Forget everything i said, now i realize that i didn't understand the problem very well. Francisco Reinaldo is right, use LEFT JOIN. Sorry for having replied that! On Thu, 2002-07-25 at 20:40, Kevin wrote: Hello, I have a query that runs perfectly until one of the items has no value or is set to 0: SELECT item.*, color.Name AS COLOR, shapecode.Shape AS SHAPE, clarity.Name AS CLARITY FROM item, color, shapecode, clarity WHERE clarity.ID = item.CLARITY_ID AND shapecode.ID = item.SHAPE_ID AND item.COLOR_ID = color.ID AND ITEM_ID='MA603' If the item.CLARITY_ID has no value or a value of 0 (which there is no defined value for in the clarity table), then the query fails. Can anything be done without adding more login in the code? Thanks -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: fulltext searching / BUG report
sergei, no i meant loading it up from raw data again. in the sense, i empty the database, then i have a script which takes a directory full of files and reads them into the database. each time i do that the database ends up corrupted. is there any way to figure out where the corruption is? is it the files? different character sets (some are italian, some english, etc)? or maybe something that my script is doing? thanks, marko Quoting Sergei Golubchik [EMAIL PROTECTED]: Hi! On Jul 26, Marko Djukic wrote: Sergei, finally managed to try out this solution, resolved my out of disk space problem... and it works now! just as you found out the boolean searches work fine now... any idea what causes the corruption in the first place? different charsets? because this happens every time i load up the database from zero. it's not a horrible thing, but still a bit weird having to tell customers that they need to repair the database each time they load it up. Strange. I dumped the whole table with mysqldump, and loaded it up again. No bug - works fine for me. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: increment function
hello there, Is there a way to select an increment function so it returns an index which is increemented by one for each row and that could be used to do some calculations with other fields? Example: select incr() as INDE, date_add(date_col, interval (INDE-1)*7 day) from suchtable where date_col = '2001-02-27'; I can workaround by selecting columns/rows into an empty table with an auto_increment column and then do the calculations there but this is not quite ideal. cheers, Zac 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: Is there an older version of MySQL for DOS (or Win 3.x)?
At 13:17 -0500 7/26/02, [EMAIL PROTECTED] wrote: Does anyone know of an older version of MySQL that runs on DOS (or on Windows 3.x)? The earliest version mentioned in the manual is Win95. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 search question
I am currently working on a website that is implemented using PHP and MySQL. The site currently has a simple search engine that allows a shopper to type in a search string that is stored in $search. For example, if a shopper types in 1972 Ford Mustang $string =1972 Ford Mustang Using the following SQL statement: SELECT * FROM whatevertable WHERE whatevercolumn LIKE '%$search% Records are returned that have this exact string and in this exact order (I'm aware a wild card character is included on the front and back of the string). My desire is to be able to logically AND each token of the search together independent or the order of the tokens. I want to return all records that have Mustang AND 1972 AND Ford. Since a shopper inputs the search string in advance I don't know how many tokens will be used. I would appreciate any suggestions. Regards, Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Odd Question on automatic start of Mysql upon boot
At 10:24 -0700 7/26/02, David Kramer wrote: I automated the MYSQL start up process using the setup command. the setup command? Everything works fine except that I noticed safe_mysql is the daemon running, shouldnt this be mysqld or mysql.server? I suppose it depends on what the setup command does... If it installs mysql.server, what you observe wouldn't be unusual, because mysql.server starts safe_mysqld, which starts mysqld. Im completely lost here, and I feel running Safe_mysql is a major security issue? Why? What problems do you think this causes? Anyone's thoughts on this? Thx, DK David Kramer Software Developer Reflect.com Direct: 415.369.4856 Cell: 650.302.7889 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: max_connections recovery
At 11:17 -0700 7/26/02, Troy Hakala wrote: ok, i turns out I have lots of sleeping connections. This is because I'm using PHP's mysql_pconnect which opens persistent connections so the next connect (on the same process) will reuse the connection. this does appear to improve performance overall, which is good. but it seems to leave lots of sleeping processes on the SQL server. so my question is this: do sleeping connections ever get reused by MySQL? or do they just timeout eventually and waste resources while they're sleeping? Are those different questions? :-) Yes, they get reused -- when they timeout and the server closes them, freeing a connection slot. Use mysql_connect() instead. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: security
At 15:24 -0300 7/26/02, Anderson Pereira Ataides wrote: Why should I close port 3306 used by mysql? What would happen if a hacker use this port? You may want to close it at your firewall, if you want to allow the server to be used only by machines on the local side of the firewall. That will prevent anyone of the non-local side from connecting to the server and possibly gaining access to your databases. Anderson Pereira Ataides - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: max_connections recovery
Pada Fri, 26 Jul 2002 11:17:08 -0700 Troy Hakala [EMAIL PROTECTED] menulis : ok, i turns out I have lots of sleeping connections. This is because I'm using PHP's mysql_pconnect which opens persistent connections so the next connect (on the same process) will reuse the connection. this does appear to improve performance overall, which is good. but it seems to leave lots of sleeping processes on the SQL server. so my question is this: do sleeping connections ever get reused by MySQL? or do they just timeout eventually and waste resources while they're sleeping? Thanks! on my server, recently i also get too many connection or can't create more thread more frequent. after doing investigation few times, i found that it can be caused by problem on our tables. and can be solved by repairing the table. and also to avoid this sleeping threads, you can set the interactive_timeout to reasonable value or run command to kill sleep/idle processes which sleeping over sometimes. -- Let's call it an accidental feature. -- Larry Wall MySQL 3.23.51 : up 35 days, Queries : 355.738 per second (avg). -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP : Jl Kapt. Tendean No. 34 - Jakarta Selatan (12790) Phone : +62 21 79199577 - Web : http://1rstwap.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: Possible bug?
Pada Fri, 26 Jul 2002 07:59:37 -0700 Rich Amick [EMAIL PROTECTED] menulis : Searched G for mysqld_list_processes: The above happens if a new user logs in at the same time you do mysql_list_processes(). This is fixed in the newest MySQL 3.22 version! --We are using version 3.23.51 - shouldn't be a problem? Still !!! :D Don't do show processlist TOO OFTEN !!! It can caused the crashes ... Before this I run : show processlist every one minutes --- my server every 2 week restart :)) Now, I'm doing show processlist only once per hour --- see my signature ... still holding on ;-) -- Let's call it an accidental feature. -- Larry Wall MySQL 3.23.51 : up 35 days, Queries : 355.724 per second (avg). -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP : Jl Kapt. Tendean No. 34 - Jakarta Selatan (12790) Phone : +62 21 79199577 - Web : http://1rstwap.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: security
Pada Fri, 26 Jul 2002 15:24:58 -0300 Anderson Pereira Ataides [EMAIL PROTECTED] menulis : Why should I close port 3306 used by mysql? What would happen if a hacker use this port? Hemmm ... if you close the port 3306, so where do you put the mysqld to LISTEN on ? Well, if you considering the security ... you should implement a good mysql privileges. For example, if you running mysql and only be accessed locally / localhost client. You should never had a grant access for a user which came from hosts beside localhost. So even you just telnet port 3306 from other computer, the connection will be refused :D -- Let's call it an accidental feature. -- Larry Wall MySQL 3.23.51 : up 35 days, Queries : 355.724 per second (avg). -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP : Jl Kapt. Tendean No. 34 - Jakarta Selatan (12790) Phone : +62 21 79199577 - Web : http://1rstwap.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: HANDLER
Hi. On Fri 2002-07-26 at 16:33:40 +0100, [EMAIL PROTECTED] wrote: Does anybody have any PHP examples of how to use the mySQL HANDLER command in place of a select statement? From the manual it appears as a high speed version of SELECT. Not to sound harsh, but IMHO, if you have to ask, you should refrain from using it. It's low level stuff. If you are in need of extra speed, there are usually a lot of normal optimization possibilities. The manual has a chapter on it. Greetings, Benjamin. -- [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: security
Hi. On Fri 2002-07-26 at 15:24:58 -0300, [EMAIL PROTECTED] wrote: Why should I close port 3306 used by mysql? What would happen if a hacker use this port? You should close it (as far as reasonable only, of course), simply, because you lose nothing, but gain an additional layer a malicious hacker has to overcome. Where closing can mean to use --skip-networking, if you have only local accesses, use a firewall to restrict connections to the local net, or allow only some computers from the internet - depending on your needs. It is a general security measure to disallow anything which is not explicitly needed, as far as the effort is reasonable regarding the needs. It is irrelvant if there are known attack vectors or not. That said, AFAICT, there are no known MySQL relevant weeknesses having the port open. Of course, you get your usual share of risks, like weak passwords, potential DoS, information leaking and so on, which have nothing to do with MySQL per se. Greetings, Benjamin. -- [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: 4.0.2 Replication problem w/ AUTO_INCREMENT columns?
Jon, replication basically pipes into the mysql client the SQL statements in the output of mysqlbinlog hostname-bin.xxx Can you study from the output what was happening when row 6781602 was to be inserted? You should find SET INSERT_ID=6781602 in the output. If you pipe the output to the mysql client, can you repeat the strange behavior? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Jon Frisby [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, July 27, 2002 4:15 AM Subject: 4.0.2 Replication problem w/ AUTO_INCREMENT columns? Hello all, We're experiencing a bit of difficulty replicating from a 4.0.2 master on Linux to a 4.0.2 slave on Linux. Most of our tables, including the one that's giving us headaches are InnoDB. My colleague encountered the problem, his description follows: It seems to barf on auto_increment fields, at least in some cases. It thinks there's a duplicate key where there isn't one: error 'Duplicate entry '6781602' for key 1' on query 'INSERT INTO visitor(partner_id, campaign_id, referrer_id, when_firstvisit) VALUES(NULL, NULL, NULL, NOW())' [From the slave:] mysql select * from visitor where id=6781602; Empty set (0.00 sec) [From the master:] mysql select * from visitor where id=6781602; +-++-+-+-+ | id | partner_id | campaign_id | referrer_id | when_firstvisit | +-++-+-+-+ | 6781602 | 17 |NULL |NULL | 2002-07-26 00:28:12 | +-++-+-+-+ Any ideas? -JF - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
display stuck .... ! ! ! !
hi guyx
i ve a lil problem
i run this query n get a result set
$query = SELECT lang_id,strng_id, strng FROM strngx where pg_id = 6 and
lang_id = '$lang_id ';
$query_result_handle = mysql_query ($query)
or die ('qry failed ! DA tbl must xixt in DA db specifyd bov ');
exe and store it in $row
for( $v=1; $v mysql_num_rows($query_result_handle); $v++)
{
$row = mysql_fetch_row($query_result_handle);
$str[ $row[0] ][ $row[1] ] = $row;
}
alls well
but
i want to get a single record displayed
when i do
echo($row[2].br);
i get all the records in $row[2]
but i want jst 1
what should i do ?
how should i go about it ?
plx plx help
m stuck ...
:(
thnx a million . billion
stuck
toby
_
Send and receive Hotmail on your mobile device: http://mobile.msn.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: question about varchars
desmond one of my teachers at college once told me to give as less a digit as possible that is give it jst a BIT more den i need coz it takes up space . :S ... will someone correct me if im rong here . pleez toby Hello there Mysql documentation says the following: values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. So, then would there be anything wrong with making all my varchar's up to 255. That is defining most of my fields to be varchar(255) even when i know they may only go 100? Thanks Desmond _ Send and receive Hotmail on your mobile device: http://mobile.msn.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 _ Chat with friends online, try MSN Messenger: http://messenger.msn.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
Date Comparison Problem...
Just when I thought I was getting the hang of this mysql stuff, the simplest
of tasks
is causing me to think about pulling my hair out.
My table:
NEWS {
newsID (INT(3), AUTO-INCREMENT),
newsTitle (VARCHAR (50)),
newsText (BLOB),
newsDate (DATE)
}
I just want to filter OUT any rows that have a 'future' date value in
'newsDate' column.
(ie. Don't select any news that hasn't happened yet!)
Query, I gather the WHERE clause deals with -MM-DD formatted dates:
SELECT * FROM news
WHERE newsDate DATE_ADD(CUR_DATE(), INTERVAL 1 DAY)
ORDER BY newsDate DESC
If I take out the WHERE clause the whole table gets SELECT-ed, it works
fine:
SELECT * FROM news
ORDER BY newsDate DESC
Any assistance gratefully Rx'd. I have DuBois's MySQL, it's obviously too
good for me.
Also tried various searches on MySQL.com, with little joy, aahh!
Tom.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ADD: Date Comparison Problem...
Very interesting addition to message...
Forgot to mention what the problem-query produces: Nothing.
Cheers,
Tom.
-Original Message-
From: Tom Norwood [mailto:[EMAIL PROTECTED]]
Sent: 27 July 2002 19:39
To: mysql-list
Subject: Date Comparison Problem...
Just when I thought I was getting the hang of this mysql stuff, the simplest
of tasks
is causing me to think about pulling my hair out.
My table:
NEWS {
newsID (INT(3), AUTO-INCREMENT),
newsTitle (VARCHAR (50)),
newsText (BLOB),
newsDate (DATE)
}
I just want to filter OUT any rows that have a 'future' date value in
'newsDate' column.
(ie. Don't select any news that hasn't happened yet!)
Query, I gather the WHERE clause deals with -MM-DD formatted dates:
SELECT * FROM news
WHERE newsDate DATE_ADD(CUR_DATE(), INTERVAL 1 DAY)
ORDER BY newsDate DESC
If I take out the WHERE clause the whole table gets SELECT-ed, it works
fine:
SELECT * FROM news
ORDER BY newsDate DESC
Any assistance gratefully Rx'd. I have DuBois's MySQL, it's obviously too
good for me.
Also tried various searches on MySQL.com, with little joy, aahh!
Tom.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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 manual for Palm OS
Hi, Would it be possible to have a MySQL manual (documentation) in .pdb or .pdf (for palm) so that it could be read on handhelds? It's a nice pocket reference. Thanks. Ashifi. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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]: TRUNCATE TABLE
Hello Benjamin, BP Hi. BP First, when you start an independend question, please start a new BP thread, but at least change the subject accordingly. This is not independent question. Manual say what TRUNCATE TABLE will recreate table from frm file, but when i use TRUNCATE TABLE, index file will not recreated, becouse it have old data and it size is biger than after CREATE TABLE ... If table recreated from table.frm why it not recreate indexes file? BP Because this is the documented behaviour: BP http://www.mysql.com/doc/C/R/CREATE_TABLE.html ;-) I spoke about TRUNCATE TABLE, not about CREATE ... SELECT ... Best regards, Andrew Sitnikov e-mail : [EMAIL PROTECTED] GSM: (+372) 56491109 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Slow queries
Thanks Mike! Yes, the query is certainly using tmp tables for the longest of times. I will try and implement the solution you provided and see how it goes. If I do, I will update you with the results. Regards, Bhavin. - Original Message - From: mos [EMAIL PROTECTED] To: Bhavin Vyas [EMAIL PROTECTED] Sent: Friday, July 26, 2002 2:21 PM Subject: Re: Slow queries At 05:53 PM 7/24/2002, you wrote: Hello, I am running mysql in our production environment, which have a few reporting tables with millions of rows. Certain queries cascade 2 such tables that have millions of rows and there by, there are extremely slow, sometimes taking over 10 minutes. However, we are ok with that since the size of the data is extremely large. However, the problem that we are facing is that when these queries are running, other queries, *unrelated to the 2 large tables* are also put on hold, so they can't be executed either and basically mysql becomes unresponsive until I kill the SLOW query and then everything is back to normal. Anybody knows why this happens and if there is a solution for this (like maybe, reprioritizing the slow query to a very low priority or something). I would think that mysql would independently try to execute the other queries but that does not seem to be happening. Thanks, Bhavin. Bhavin, If MySQL has to create temporary tables to do the join, then of course things are going to slow down quite a bit. I've been thinking about a solution and this may help (I haven't tried it). Why not create a RAM disk and have your MySQL TmpDir point to that? RAM is pretty cheap and a 30-100MB RAM drive would speed up table joins quite a bit because it can be written to 10x faster than a hard drive. There are a lot of 3rd party software RAM drives out there for Windows and Linux. You would have to specify a RAM drive large enough to hold all your temporary files. There are also RAM drive PCI cards and external RAM drive devices but these cost a lot more than a software RAM drive. These devices also let you have a RAM drive of 4g or more without taking memory away from the OS. Their data is persistent between reboots via an external battery. MONTY: It would be nice if TmpDir could accept more than 1 directory, like a delimited string of directories as in o:\RAM;m:\MySQLTmp1;o:\MySQLTmp2 so when one runs out of disk space, it looks for another directory in the path. Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Access denited error
Dear Friends, I am having a local Linux server which have MySQL database. I am loging to MySQL by administrative user root. I have two tables in the database lngsnet, the table names are count and product. I want to load the text file which have 25 lines / records which is stored in the /tmp directory into the product table. Using the following coding, I was able to display the content from the table count and load the content of the text file product.txt into the table product successfully. ?php mysql_connect(localhost,root,vinodb) or die (cannot connect to mysqld); mysql_select_db(lngsnet) or die(cannot select database); $result = mysql_query(select * from count); $row = mysql_fetch_row($result); echo(COUNT=$row[0]); echo(br); $query= load data local infile /tmp/product.txt into table product fields terminated by ,; mysql_query($query) or die(mysql_error()); ? h2Data successfully insertedh2 Now I have a web space www.lngs.net with mysql support. In the web, I have created the same database and table on web. I have granted the permission to Select, up- date, Insert etc.. to the database lngsnet. The web is having PHPmyadmin support. Using PHPmyadmin, I am able to load the text file into the product table which is on the web. The text file is in my local hard disk. When I checked the 25 records are inserted into the table. Subsequently I have loaded above codings (post_data.php) on the web and the product.txt file into the /tmp directory which is available on web. This /tmp directory is having read, write, delete, execute permissions. Now the error is I am able to display the content of the table count on the screen. When it is executing the load query, it is showing the error on the browser that COUNT=006 Access denied for user:root@localhost (Using Passwor- dYES) Please suggest me how to over come the problem Regards, Vinod - Sify Mail - now with Anti-virus protection powered by Trend Micro, USA. Know more at http://mail.sify.com One click here and you could be counting money! StreetsCall from Walletwatch.com. Subscribe now! http://www.walletwatch.com/cgi-bin/ww/walletwatch/equity/news_articles/news_detail.jsp?oid=11658894 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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.51 problems ? (with snortreport)
Hi, I have installed apache-mysql-php with the apachetoolbox.com script. (www.apachetoolbox.com) I have first installed everything with keeping mysql 3.23.49(rpm), everything was fine then, now i have used apachetoolbox to install mysql 3.23.51 and now i have a problem. I use Snort-Mysql to report network intrusions in the database and I have installed SnortReport to display the intrusion alerts with this php driven website. after i upgraded to mysql 3.23.51 i get following error when i click on 'summary' of the events.. assertion failed invalid signature in file details.php looks like some kind of mysql bug, are there more problems known with this latest version ? any ideas ? bart - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 manual for Palm OS
[EMAIL PROTECTED] wrote: Would it be possible to have a MySQL manual (documentation) in .pdb or .pdf (for palm) so that it could be read on handhelds? It's a nice pocket reference. Something like http://www.mysql.com/Downloads/Manual/manual.pdf ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
PHP/MySQL Search Engine Query Question
I am currently working on a website that is implemented using PHP and MySQL. The site currently has a simple search engine that allows a shopper to type in a search string that is stored in $search. For example, if a shopper types in 1972 Ford Mustang $string =1972 Ford Mustang Using the following SQL statement: SELECT * FROM whatevertable WHERE whatevercolumn LIKE '%$search% Records are returned that have this exact string and in this exact order (I'm aware a wild card character is included on the front and back of the string). My desire is to be able to logically AND each token of the search together independent or the order of the tokens. I want to return all records that have Mustang AND 1972 AND Ford. Since a shopper inputs the search string in advance I don't know how many tokens will be used. I would appreciate any suggestions. Regards, Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help me out here guys... you gotta have a primary key
k This is seems ridiculous to me,.. but i'll let you tell me if i'm just being stubborn. So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? Thanks Desmond (sql) _ Send and receive Hotmail on your mobile device: http://mobile.msn.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
transaction issue
Hello everyone, We can find at mysql documentation, that using transaction may slow down up to 4 times mysql. This because working with transfaction means to do a lot of things while updating tables. I'm currently working in a system where in some cases (very few cases) I realy need to use transactions. So, only in those cases I use transaction tables (Innodb). And also, I still work in autocommit mode. Only when I need transaction I use BEGIN/COMMIT/ROLLBACK. My question is, am I improving performance with that? Or just by using the transaction tables it means my performance will go down anyway? Also, working without transaction (autocommit) in transaction tables whenever possible makes any difference? Thanks a lot, Joao. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: question about varchars
i think it's just a length limiter in serversite (mysql server) string longer than this limit will be cut off allowing you to be lazy to cut it off in clientsite (mysql client) and also a way to indicate the string length, don't have to write a txt file for document From: toby - To: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: question about varchars Date: Sat, 27 Jul 2002 05:20:01 + desmond one of my teachers at college once told me to give as less a digit as possible that is give it jst a BIT more den i need coz it takes up space . :S ... will someone correct me if im rong here . pleez toby Hello there Mysql documentation says the following: values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. So, then would there be anything wrong with making all my varchar's up to 255. That is defining most of my fields to be varchar(255) even when i know they may only go 100? Thanks Desmond _ Send and receive Hotmail on your mobile device: http://mobile.msn.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 To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Chat with friends online, try MSN Messenger: http://messenger.msn.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 To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ ÓëÁª»úµÄÅóÓѽøÐн»Á÷£¬ÇëʹÓà MSN Messenger: http://messenger.microsoft.com/cn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Date Comparison Problem...
Just when I thought I was getting the hang of this mysql stuff, the simplest
of tasks
is causing me to think about pulling my hair out.
My table:
NEWS {
newsID (INT(3), AUTO-INCREMENT),
newsTitle (VARCHAR (50)),
newsText (BLOB),
newsDate (DATE)
}
It's better to use copy and paste when reporting code - what you
show there is illegal syntax.
I just want to filter OUT any rows that have a 'future' date value in
'newsDate' column.
(ie. Don't select any news that hasn't happened yet!)
Query, I gather the WHERE clause deals with -MM-DD formatted dates:
SELECT * FROM news
WHERE newsDate DATE_ADD(CUR_DATE(), INTERVAL 1 DAY)
ORDER BY newsDate DESC
That's illegal, too. There is no CUR_DATE() function, it's CURDATE().
Other than that, it looks okay. If it produces no rows, I would assume
that means that you have no news that is not in the future, but I guess
that's probably not true. You can see what dates are being compared
like this, which may help:
SELECT newsDate, DATE_ADD(CURDATE(),INTERVAL 1 DAY) FROM news;
Also, I expect that your query would be simpler like this:
SELECT * FROM news WHERE newsDate = CURDATE() ORDER BY newsDate DESC;
(Note the = rather than ...)
If I take out the WHERE clause the whole table gets SELECT-ed, it works
fine:
SELECT * FROM news
ORDER BY newsDate DESC
Any assistance gratefully Rx'd. I have DuBois's MySQL, it's obviously too
good for me.
Also tried various searches on MySQL.com, with little joy, aahh!
Tom.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help me out here guys... you gotta have a primary key
Desmond, A primary key is needed to maintain a unique identity of each record and there by help in linking it to other records in other tables. However, we have certain tables which link one table to another (needed because of absence of foreign keys in mysql) which don't necessarily have primary keys. If you know that a column can have only unique records you are better off describing it as unique or primary key, not depending upon your code to always do that because there is a good chance that your code can have bugs and then all your data might turn out to be invalid. So the bottom line is that you don't *need* a primary key but if u know that a column will be unique then, do define it as unique or primary. Primary keys will certainly help in reducing redundant data as long as they have some meaning (Social security number, telephone number, etc). Bhavin. - Original Message - From: Desmond Lee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, July 27, 2002 7:26 PM Subject: help me out here guys... you gotta have a primary key k This is seems ridiculous to me,.. but i'll let you tell me if i'm just being stubborn. So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? Thanks Desmond (sql) _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help me out here guys... you gotta have a primary key
Hello Desmond, I am not an expert in DB, but I support your ideas. The primery key is one of the fundamental concept of database. With the key, you can refer faster to the record that you are looking for. Even you have a perfect codes, you will be not able to cover all senario that your software to to deal with. May suggest your partner to do some research on database design ! Good luck ! /Khanh Desmond Lee wrote: k This is seems ridiculous to me,.. but i'll let you tell me if i'm just being stubborn. So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? Thanks Desmond (sql) _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: help me out here guys... you gotta have a primary key
Well, there are people who feel that tables should be linked by foreign keys to ensure referential integrity. Everyone who uses MySQL gets by without them though. Its up to your coding to make sure referential integrity is not violated. Although foreign keys are left out of MySQL for performance reasons, and there is ussualy not much performance hit for having a primary key and most of the time there is probably a performance gain. So I think generally a primary key is a good thing, unless you have a strange situation where for performance reasons (speed or size) a primary key doesn't make sense. Dave -Original Message- From: Desmond Lee [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 27, 2002 9:27 PM To: [EMAIL PROTECTED] Subject: help me out here guys... you gotta have a primary key k This is seems ridiculous to me,.. but i'll let you tell me if i'm just being stubborn. So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? Thanks Desmond (sql) _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: help me out here guys... you gotta have a primary key
Hi, Is it such a big deal to use more than one field for a primary key? Two field keys are only a little slower than single field Primary keys for selects. Often in linking tables you want to have duplicates of the two foreign keys right? I just wish people would stop making 100 field tables, then I would be happy. Ever do an insert on a 78 row table that has all fields set to NOT NULL, yet you only need to insert about 15 columns worth of data? It isn't pretty.. And BTW, do people not ever read the mySQL site? Ever heard of InnoDB, Berkely DB? Yes, foreign keys, even cascade deletes. Is this going to become one of those Perl sucks because it is CGI kind of things? mySQL truly rocks, it keeps getting better and better. Actualy I started a project recently where I thought I would go ahead be a good boy, and use foreign keys, it ends up that I just can't think of a good reason for it in my particular situation. I was kind of disappointed really.. But I am very happy to know that at any time, if I want transactions and foreign keys, it is a few keystrokes away. Eric PS why oh why do people make 100 field tables! At 11:35 PM 7/27/02 -0500, Dave Dutcher wrote: Well, there are people who feel that tables should be linked by foreign keys to ensure referential integrity. Everyone who uses MySQL gets by without them though. Its up to your coding to make sure referential integrity is not violated. Although foreign keys are left out of MySQL for performance reasons, and there is ussualy not much performance hit for having a primary key and most of the time there is probably a performance gain. So I think generally a primary key is a good thing, unless you have a strange situation where for performance reasons (speed or size) a primary key doesn't make sense. Dave -Original Message- From: Desmond Lee [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 27, 2002 9:27 PM To: [EMAIL PROTECTED] Subject: help me out here guys... you gotta have a primary key k This is seems ridiculous to me,.. but i'll let you tell me if i'm just being stubborn. So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? Thanks Desmond (sql) _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Set different sizes on a per-database level?
I have been searching and searching, and maybe this is a 4.0 thing, but is there a way, on a case by case basis, to pre-define a size limit for a MySQL database? In other words, make one 50MB and the next one make 100MB on the same server? I've seen indications you can set the default size for all databases, but I want it on a case by case basis. Any ideas, pointers, workarounds? Thanks for any help! Bill - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: help me out here guys... you gotta have a primary key
If it's for a linking table, you'd just have something like this: CREATE TABLE linking_table ( first_id INTEGER, second_id INTEGER, PRIMARY KEY (first_id, second_id) ); For an n-m relationship, that's the only way to do it. Dean Harding. P.S. The syntax may be wrong, that's just off the top of my head, but you get the idea - a primary key doesn't have to be on one field only, it can be on as many as you need. -Original Message- From: Desmond Lee [mailto:[EMAIL PROTECTED]] Sent: Sunday, 28 July 2002 12:27 pm To: [EMAIL PROTECTED] Subject: help me out here guys... you gotta have a primary key k This is seems ridiculous to me,.. but i'll let you tell me if i'm just being stubborn. So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? Thanks Desmond (sql) _ Send and receive Hotmail on your mobile device: http://mobile.msn.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 mysql-unsubscribe- [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
Set different sizes on a per-database level?
I have been searching and searching, and maybe this is a 4.0 thing, but is there a way, on a case by case basis, to pre-define a size limit for a MySQL database? In other words, make one 50MB and the next one make 100MB on the same server? I've seen indications you can set the default size for all databases, but I want it on a case by case basis. Any ideas, pointers, workarounds? Thanks for any help! Bill - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Set different sizes on a per-database level?
In the last episode (Jul 28), Bill Leonard said: I have been searching and searching, and maybe this is a 4.0 thing, but is there a way, on a case by case basis, to pre-define a size limit for a MySQL database? In other words, make one 50MB and the next one make 100MB on the same server? I've seen indications you can set the default size for all databases, but I want it on a case by case basis. The only way to limit size is to set a quota on the filesystem holding the tables. If you chown each database to a different uid, but leave them as group mysql, you should be able to assign per-user quotas for each database. This will only work if you don't allow the user to create arbitrary tables, as their uid will be wrong. FreeBSD has a special 'suiddir' mount flag, though, that will automatically chown new files to the owner of the parent directory. -- Dan Nelson [EMAIL PROTECTED] 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
