Re: slow response time
In article [EMAIL PROTECTED], Jeremy Zawodny [EMAIL PROTECTED] writes: Using the query cache at all? Not sure... I'm using the values for caches and whatnot from the my-large.cnf in the distribution. The my-large.cnf I'm looking at has a 16M query cache, but doesn't explicitly turn it on. See what show variables like 'query_ca% says: mysql show variables like 'query_ca%'; +---+--+ | Variable_name | Value| +---+--+ | query_cache_limit | 1048576 | | query_cache_size | 33554432 | | query_cache_type | DEMAND | +---+--+ 3 rows in set (0.01 sec) You see demand there because we set query_cache_type = 2. But if you had = 1 you should see either ON or ENABLED, I don't remember which. If not, it's probably OFF or DISABLED. The query cache might help somewhat when looking up users/passwords, but it won't help at all with the relay table because it changes too often. Moreover, if your analysis is correct, Charles does not have a MySQL problem at all, so I wouldn't bother to mess with it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select and where clause - help
Hi, | BT-1034.02 mysql SELECT * FROM balloon_txt WHERE access_no = 'BT-1034.2'; I think you have a type .. instead of SELECT * FROM balloon_txt WHERE access_no = 'BT-1034.2' try SELECT * FROM balloon_txt WHERE access_no = 'BT-1034.02' HTH, Markus - Original Message - From: leegold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 4:51 AM Subject: select and where clause - help I am trying to get a select statement with WHERE to work and I can't seem to do it. Below find some output. I try a select w/WHERE by book but it doesn't work. Originally I tried in PHP and didn't work, so now I just want the MYSQL to work first, I could be overlooking something simple since I'm new to MYSQL. Thanks, Lee G. mysql show columns from balloon.balloon_txt; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | access_no | varchar(20) | | PRI | | | | recs_txt | text| YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql SELECT * FROM balloon_txt limit 0,1; +--+ -- -- -- -- --+ | access_no| recs_txt | +--+ -- -- -- -- --+ | BT-1034.02 | Title_[ Balloon Capabilities and Futures] Author[ Thomas W. Kelly Resp_Org__[ Air Force Cambridge Research Labs. FundingOrg[ Date__[ Dec 1963 Report_No_[ Found in: AFCRL-TR-63-919, AFSG No. 154 Reposit_No[ Found in: AD-614 065 ContractNo[ Descript__[ Conference Paper, 25 p Notes_[ This symposium was held in Boston, MA on 25 to 27 September 1963. Twenty papers were presented, of which 16 were published. This paper is found on pp 3-27.] Subj_Terms[ Balloon technology, tethered balloons, payload orientation, hot air balloons, balloon design, manned balloons, instrumentation] Content___[ The papers covered present load and altitude capabilities of many types of plastic balloons. Objectives of current research in balloon technology are described, including extension of present capabilities, increased reliability, and longe r duration.] ElecAccess[ http://library/Databases/Balloon/Data/BT1034.02.pdf Avail_From[ WFF/BTL | +--+ -- -- --
User variables optimization of IF()
Hi everybody. I generally try to parametrize out my queries as much as possible with user-variables. So, say you have a general query for all users: --- SELECT ... FROM users --- It's nice to do this: SET @USER_ID:= NULL; SELECT ... FROM users WHERE IF(@USER_ID IS NOT NULL,[EMAIL PROTECTED],1) --- This *works*, but when you set @USER_ID to a number, the 4.1 optimizer doesn't optimize it very well: it does a table-scan, instead of using the index on user_id. Has anyone got a good alternative that will do less table-scans? Or, if a developer is listening, can you give us details on how the optimizer deals with IF()? Thanks. Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Solaris Performance Issue
Hi, My old mysql is 3.23.56 on RedHat9(Intel). After moving mySQL to Solaris 9 (Sun F280R/2GB Memory) and upgrading mySQL to 4.0.20, I got a lot of complaints about the performance for select and update. Have anyone can tell me if there is anything I should tune for Solaris or MySQL to improve the performance. Thanks //Linda The variables are as below: Variable_name Value back_log50 basedir /usr/mysql/ binlog_cache_size 32768 bulk_insert_buffer_size 8388608 character_set big5 character_sets big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb 2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin1 latin1_de la tin2 latin5 sjis swe7 tis620 ujis usa7 win1250 win1251ukr win1251 concurrent_insert ON connect_timeout 5 convert_character_set datadir /usr/mysql/var/ default_week_format 0 delay_key_write ON delayed_insert_limit100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 0 ft_boolean_syntax + -()~*:| ft_min_word_len 4 ft_max_word_len 254 ft_max_word_len_for_sort20 ft_stopword_file(built-in) have_bdbNO have_crypt YES have_innodb DISABLED have_isam YES have_raid NO have_symlinkYES have_opensslNO have_query_cacheYES init_file innodb_additional_mem_pool_size 1048576 innodb_buffer_pool_size 8388608 innodb_data_file_path innodb_data_home_dir innodb_file_io_threads 4 innodb_force_recovery 0 innodb_thread_concurrency 8 innodb_flush_log_at_trx_commit 1 innodb_fast_shutdownON innodb_flush_method innodb_lock_wait_timeout50 innodb_log_arch_dir innodb_log_archive OFF innodb_log_buffer_size 1048576 innodb_log_file_size5242880 innodb_log_files_in_group 2 innodb_log_group_home_dir innodb_mirrored_log_groups 1 innodb_max_dirty_pages_pct 90 interactive_timeout 28800 join_buffer_size131072 key_buffer_size 1073741824 language/usr/mysql/share/mysql/english/ large_files_support ON license GPL local_infileON locked_in_memoryOFF log OFF log_update OFF log_bin ON log_slave_updates OFF log_slow_queriesOFF log_warningsON long_query_time 10 low_priority_updatesOFF lower_case_file_system OFF lower_case_table_names 0 max_allowed_packet 8387584 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 1000 max_connect_errors 200 max_delayed_threads 20 max_insert_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_user_connections0 max_tmp_tables 32 max_write_lock_count4294967295 myisam_max_extra_sort_file_size 268435456 myisam_max_sort_file_size 2147483647 myisam_repair_threads 1 myisam_recover_options OFF myisam_sort_buffer_size 67108864 net_buffer_length 8192 net_read_timeout30 net_retry_count 10 net_write_timeout 60 new OFF open_files_limit5010 pid_file/usr/mysql/var/mysqld.pid log_error port3306 protocol_version10 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_size0 query_cache_typeON query_prealloc_size 8192 range_alloc_block_size 2048 read_buffer_size8384512 read_only OFF read_rnd_buffer_size262144 rpl_recovery_rank 0 server_id 1 slave_net_timeout 3600 skip_external_locking ON skip_networking OFF skip_show_database OFF slow_launch_time2 socket /tmp/mysql.sock sort_buffer_size8388600 sql_mode0 table_cache 1024 table_type MYISAM thread_cache_size 10 thread_concurrency 10 thread_stack196608 tx_isolationREPEATABLE-READ timezoneCST tmp_table_size 33554432 tmpdir /var/tmp/ transaction_alloc_block_size8192 transaction_prealloc_size 4096 version 4.0.20-log version_comment Source distribution version_compile_os sun-solaris2.9 wait_timeout28800
C API mysql_next_result stalls
I know that I should use mysqlbug tool but it doesn't work. Description: MySQL C API mysql_next_result stalls in my program. I'm using pthread and MYSQL handle is using in just one thread. I hacked into the source and found this call is just same as mysql_real_query except it doesn't send query to server, Hope this bug will be fixed soon. Thanks for great masterpiece How-To-Repeat: so, the step was 1. mysql_send_query 2. cli_read_query_result 3. mysql_store_result 4. Do my thing 5. mysql_next_result calls cli_read_query_result again and it stops at net_safe_read Fix: I could not. Submitter-Id: [EMAIL PROTECTED] Originator:User Organization: Ymir Entertainment. MySQL support: none Synopsis: C API mysql_next_result stalls. Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-4.1.3-beta (FreeBSD port: mysql-client-4.1.3) Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.1.3-beta, for portbld-freebsd5.2 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.1.3-beta Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 1 day 12 hours 27 min 27 sec Threads: 19 Questions: 1872618 Slow queries: 5 Opens: 128 Flush tables: 1 Open tables: 59 Queries per second avg: 14.268 C compiler:cc (GCC) 3.3.3 [FreeBSD] 20031106 C++ compiler: c++ (GCC) 3.3.3 [FreeBSD] 20031106 Environment: machine, os, target, libraries (multiple lines) System: FreeBSD metin2test.metin2.co.kr 5.2-RELEASE FreeBSD 5.2-RELEASE #1: Sat Mar 13 14:24:30 KST 2004 [EMAIL PROTECTED]:/usr/src/sys/i386/compile/MYKERNEL i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Configured with: FreeBSD/i386 system compiler Thread model: posix gcc version 3.3.3 [FreeBSD] 20031106 Compilation info: CC='cc' CFLAGS='-O -pipe -mcpu=pentiumpro -D_THREAD_SAFE -O3 -fno-omit-frame-pointer -fno-gcse' CXX='c++' CXXFLAGS='-O -pipe -mcpu=pentiumpro -D_THREAD_SAFE -O3 -fno-omit-frame-pointer -fno-gcse -O -pipe -mcpu=pentiumpro -D_THREAD_SAFE -O3 -fno-omit-frame-pointer -fno-gcse -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' ASFLAGS='' LIBC: -r--r--r-- 1 root wheel 892344 1 11 11:45 /lib/libc.so.5 -r--r--r-- 1 root wheel 1730634 1 11 11:45 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 14 2 29 16:42 /usr/lib/libc.so - /lib/libc.so.5 Configure command: ./configure '--localstatedir=/var/db/mysql' '--without-debug' '--without-readline' '--without-libedit' '--without-bench' '--without-extra-tools' '--with-libwrap' '--with-mysqlfs' '--with-vio' '--with-low-memory' '--with-ndbcluster' '--with-comment=FreeBSD port: mysql-client-4.1.3' '--enable-thread-safe-client' '--with-charset=euckr' '--with-extra-charsets=all' '--with-mysqld-ldflags=-all-static' '--enable-assembler' '--with-berkeley-db' '--with-named-thread-libs=-lc_r' '--without-server' '--prefix=/usr/local' '--build=i386-portbld-freebsd5.2' 'CFLAGS=-O -pipe -mcpu=pentiumpro -D_THREAD_SAFE -O3 -fno-omit-frame-pointer -fno-gcse' 'CXX=c++' 'build_alias=i386-portbld-freebsd5.2' 'CC=cc' 'CXXFLAGS=-O -pipe -mcpu=pentiumpro -D_THREAD_SAFE -O3 -fno-omit-frame-pointer -fno-gcse -O -pipe -mcpu=pentiumpro -D_THREAD_SAFE -O3 -fno-omit-frame-pointer -fno-gcse -felide-constructors -fno-rtti -fno-exceptions' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT SELECT
Hi, Using the following query I am attampting to insert the Work_Type_IDs from Work_Types WHERE Project_ID = 'x'. However I also wan to insert the Project_ID into the table, how would this syntax work - apperently the column count is incorrect... INSERT INTO Letter_Templates (Work_Type_ID, Project_ID) SELECT Work_Types.Work_Type_ID FROM Work_Types WHERE Work_Types.Project_ID = 'x'; Thanks for your help _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT SELECT
Shaun, You need two columns for the insert, but you're only selecting one. Try this: INSERT INTO Letter_Templates (Work_Type_ID, Project_ID) SELECT Work_Types.Work_Type_ID, Work_Types.Project_ID FROM Work_Types WHERE Work_Types.Project_ID = 'x'; Andy -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: 07 July 2004 13:37 To: [EMAIL PROTECTED] Subject: INSERT SELECT Hi, Using the following query I am attampting to insert the Work_Type_IDs from Work_Types WHERE Project_ID = 'x'. However I also wan to insert the Project_ID into the table, how would this syntax work - apperently the column count is incorrect... INSERT INTO Letter_Templates (Work_Type_ID, Project_ID) SELECT Work_Types.Work_Type_ID FROM Work_Types WHERE Work_Types.Project_ID = 'x'; Thanks for your help _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connect to other database format
Elmar and Madeleine von Muralt wrote: Hi, I'm completely new to MySQL and some of its concepts, therefore, these questions may be very basic. To connect to another database format I need the ODBC driver for that other format - right? I've built a repair job entry database on my Palm with HanDBase 3.0. I also got the HanDBase ODBC Driver v3.0. I tried to connect my desktop database program Paradox to it but there are some issues which seem to be unsolvable. I tried to connect to the .pdb (HanDBase) tables with Excel - that worked. Presumably, this indicates that the HanDBase ODBC driver is doing its job. What I would like to do is to copy the .pdb tables into MySQL tables and then work with these MySQL tables on the desktop. I searched hi and lo but all the connection related info seems to be about other database programs connecting to MySQL but I could not find any info about how MySQL connects to other database formats. It doesn't. Use Access to export them to csv. Load the csv files into MySql. Thanks for any help with this. Elmar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to query an oracle table from a mysql database
Good morning, I need a MySQL database to be able to query a table in an Oracle database of a remote host. Is this possible? Could anybody tell me how to do it or where to find this information? Thanks in advance, --Claudio ..
Space is filling up
Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL IPv6 enabled ready !!!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ( I apologize for duplicate message. Just for make sure the anti-spam process) To MySQL staff and developer's team: At http://www.ngn.euro6ix.org/IPv6/mysql you will find a patch to make the last stable version on MySQL, (4.0.8) ready to run on the new protocol IPv6. Far from forking the main line of MySQL development, but with the only purpose to improve the excellence of MySQL as a GPL Open Source DBMS, I suggest you to initialize the process to incorporate this proposal into official CVS tree. A few questions: + I tried to patch the 5.X version on the web, but this seemed to be incomplete and I could not compile it on a clear way. So, the basis for the patch is 4.0.8 + Read the corresponding file readme-mysql-4.0.8-v6-20040701.txt on the same URL. You will find some open questions not so easy to solve for me, since I'm not an expert on hacking MySQL system. Future collaboration could include support on IPv6 issues from my side. You should understand that nGn, the company releasing this patch, as a partner of Euro6IX Project has not been assigned so much resources to be full time dedicated to MySQL development. Other interesting projects are in queue to be ported into IPv6. + Some tests have been achieved on a IPv6 network, fully compliant. However, a more exhaustive plan of testing should be scheduled before releasing the code. Anyway, take my gratitude and acknowledge for releasing the Open source code. It has been one oportunity to learn from such a great project like MySQL. I hope you to find it usefull ! Regards. -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQFA7BtN7cY00jhNuEcRAnpAAJ48F/eQXgJ9MxMxma25KM11D5yWGACeNovA RDz5jitHD9hNs4uTIJo2/Jo= =FkEJ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DBF to MySQL
John: I´m new to MySQL but I hope this could help. Please read below. Regards from Costa Rica -Mensaje original- De: John Mistler [mailto:[EMAIL PROTECTED] Enviado el: Martes, 06 de Julio de 2004 09:55 p.m. Para: [EMAIL PROTECTED] Asunto: Re: DBF to MySQL For some reason, the imported information showed up as garbled nonsense. The file I was importing was an .xls file. Do you know if there is another TERMINATED BY I should be using? If not, I wonder how I can find out? A/ I think you could export your xls file into a comma separated value file (CSV). In excel File/Save as/CSV then with the Notepad you can delete the first line of the csv file (if there are column headers in your xls file). For example if your xls file is like Year NumberDate Total Unit ID Unit Name 20039253 05/05/2003 -36.365,003202 Unidad Programación 20039030 06/03/2003 1 4201 Depto. Consumidores 20039055 14/03/2003 3.000,00 7020 Proyecto de Distribución 20039798 26/08/2003 3.000,00 3210Sección Transportes y Taller your csv file will be: Year;Number;Date;Total;Unit ID;Unit Name 2003;9253;05/05/2003;-36.365,00;3202;Unidad Programación 2003;9030;06/03/2003;1;4201;Depto. Consumidores 2003;9055;14/03/2003;3.000,00;7020;Proyecto de Distribución Subterránea 2003;9798;26/08/2003;3.000,00;3210;Sección Transportes y Taller If you delete the first row (headers) you will have only the raw data Then you could save your file as a txt (but I think any extension should work) Then LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE mytable FIELDS TERMINATED BY ';' ENCLOSED BY '' LINES TERMINATED BY '\n'; I think you could left out the clause ENCLOSED BY '' because this is the default Also, please note: 1) If LOCAL is specified, the file is read by the client program on the client host and sent to the server. If LOCAL is not specified, the file must be located on the server host and is read directly by the server. 2) If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINESTERMINATED BY '\r'. The other question I have is: do I have to create a table within the MySQL database with exactly the right number of columns ahead of time for the import to work? - this is what I did. If so, is there a way to import info from a .dbf or .xls file without knowing the structure of the table ahead of time? A/ As far as I know you need to know the table´s structure in advance. From the mysql manual: By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list: mysql LOAD DATA INFILE 'persondata.txt' The whole history is in: http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html Thanks, John on 7/6/04 3:59 PM, Chinchilla Zúñiga, Guillermo at [EMAIL PROTECTED] wrote: Try, for example: LOAD DATA INFILE 'myfile.cdf' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'; -Mensaje original- De: John Mistler [mailto:[EMAIL PROTECTED] Enviado el: Martes, 06 de Julio de 2004 04:51 p.m. Para: [EMAIL PROTECTED] Asunto: DBF to MySQL I am wanting to parse the info in a .dbf file (or .xls file for that matter) and place it in a table in a MySQL database. Is this something that I can do with the server side MySQL application, or do I need to figure out a way to do it on the client side? Any description of the method would be very welcome! For what it is worth, I am a Mac OSX.3 user. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
maxDB
I'm working with maxDB 7.5 and trying to figure out whether mySQL 4.0.x database files can be read by it. I'm getting the impression we will need to run a sqlimport from a mysql dump to get the data. Is this the case? Has anyone been successful in migrating to maxDB from mySQL 4.0.x? thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to query an oracle table from a mysql database
You may want to look into using a programming language such as C or Java that communicates between both databases. -Original Message- From: Alonso, Claudio Fabian To: '[EMAIL PROTECTED]' Sent: 7/7/04 8:14 AM Subject: How to query an oracle table from a mysql database Importance: High Good morning, I need a MySQL database to be able to query a table in an Oracle database of a remote host. Is this possible? Could anybody tell me how to do it or where to find this information? Thanks in advance, --Claudio .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with Mysql 4.0.18 + Apache
Hello:
This is my first time posting so bear with me. I have a Debian (woody)
server running a good sized database (7.2GB), Mysql 4.0.18. I am
running Apache 1.3.29 + perl and using mysql as the backend. Here's the
problem. I had the site up for weeks, with everything running
perfectly. Ordinarily there would be about 11 mysql processes running
(according to ps). However, when I switched my domain name over to this
server, immediately the number of mysql processes increases to over 100,
and the site crashes, giving errors like
DBI connect('yada yada, ...) failed: Too many connections at DB.pm line 25
Also I can't connect to the mysql commandline, I get the error
ERROR 1040: Too many connections.
I know that the site is getting virtually no traffic, so the problem is
not that it is being overloaded. I have tried this scenario at least a
dozen times, and the same thing always happens. Is this a known issue
with Apache + mysql 4.0.18?
Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very strange database behavior
Good morning to all.
I have a very strange problem in my database and am trying to track down
whether it is a JAVA problem or a mySQL problem.
I have a table that is supposed to accept only unique data, however, at this
time, the PK for that information is set to the auto incrementing ID field.
The unique field is called 'pat'. Here is a description of the problem.
I have a class that posts data to the pat field using java
preparedStatements. The statement executes like this
// test that the insert is unique
SELECT ID from table where pat = 'MyValue';
// If that comes back with an ID, no insert is made
// No ID means we insert.
if(NoIdReturned){
INSERT INTO table(pat) Values('MyValue');
// I have added a test here to insure that the
// data was inserted
// I perform the same query as above
// This is not in the production code.
SELECT ID from table where pat = 'MyValue';
// In tests this comes back with a value every time.
// The production code gets the last_insert_id()
Select last_insert_id() as myID;
// In tests this value and the one above always match.
}
OK. Here's the oddity, you would think that if I run this class again with
the same parameter for MyValue, the flow would be...
test for MyValue..
test returns IDExists
No insert happens.
That's not what is happening. If I run the class...
once - Insert happens
twice - Insert happens
3 times - ID is found and no insert happens.
Oddity #2 - In test 2, the first query returns no ID.
After the insert I ask for last insert ID and get 1234.
I ask for the ID using the SAME query that returned me NO ID and I get 1233,
the ID of the first insert.
I have tested this as many ways as I can think. The insert is NOT a delayed
insert. I have tried spacing out the tests. I have checked the DB using
MySQLCC AND using the query that my test prints to screen and the first
insert is always happening. Below are the actual test results from run#2.
// Query to see if the data exists.
SELECT ID from Rules where pat = 'AUTO\\_DOGGY';
Identity is -1 because no rows exist.
AUTO_DOGGY Identity was -1
// Insert the rule
Inserting new rule.AUTO_DOGGY
// test
Testing for AUTO_DOGGY
// Same query as above
SELECT ID from Rules where pat = 'AUTO\\_DOGGY';
// Below LIID = Last_Insert_ID()
// Query ID is what is returned from query
LIID = 124566 Query ID = 124565
HELP!
God Bless
Paul C. McNeil
Developer in Java, MS-SQL, MySQL, and web technologies.
GOD BLESS AMERICA!
To God Be The Glory!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
moving records between tables?
Hi all, I have 2 tables...one for keeping active items while the other is for older records. A housekeep program will come in every night to move some records (matching several criteria from the active table to the history one. I am currently doing SELECT, INSERT and then DELETE. i.e. a select * from active where key=key_value limit 1 and then a insert into history... and then a delete * from active where pri_key='pri_key_value'... I am sure there's a better way right?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query an oracle table from a mysql database
Try PHP, it has built in functions to access both DBs HTH Steve Victor Pendleton wrote: You may want to look into using a programming language such as C or Java that communicates between both databases. -Original Message- From: Alonso, Claudio Fabian To: '[EMAIL PROTECTED]' Sent: 7/7/04 8:14 AM Subject: How to query an oracle table from a mysql database Importance: High Good morning, I need a MySQL database to be able to query a table in an Oracle database of a remote host. Is this possible? Could anybody tell me how to do it or where to find this information? Thanks in advance, --Claudio .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very strange database behavior
Are you saying that you are not receiving an Unique key violation when you
enter in duplicate data or you are relying on a value being present? If you
are using the later, do you have query caching turned on?
-Original Message-
From: Paul McNeil
To: MySQL General
Sent: 7/7/04 10:43 AM
Subject: Very strange database behavior
Good morning to all.
I have a very strange problem in my database and am trying to track down
whether it is a JAVA problem or a mySQL problem.
I have a table that is supposed to accept only unique data, however, at
this
time, the PK for that information is set to the auto incrementing ID
field.
The unique field is called 'pat'. Here is a description of the problem.
I have a class that posts data to the pat field using java
preparedStatements. The statement executes like this
// test that the insert is unique
SELECT ID from table where pat = 'MyValue';
// If that comes back with an ID, no insert is made
// No ID means we insert.
if(NoIdReturned){
INSERT INTO table(pat) Values('MyValue');
// I have added a test here to insure that the
// data was inserted
// I perform the same query as above
// This is not in the production code.
SELECT ID from table where pat = 'MyValue';
// In tests this comes back with a value every time.
// The production code gets the last_insert_id()
Select last_insert_id() as myID;
// In tests this value and the one above always match.
}
OK. Here's the oddity, you would think that if I run this class again
with
the same parameter for MyValue, the flow would be...
test for MyValue..
test returns IDExists
No insert happens.
That's not what is happening. If I run the class...
once - Insert happens
twice - Insert happens
3 times - ID is found and no insert happens.
Oddity #2 - In test 2, the first query returns no ID.
After the insert I ask for last insert ID and get 1234.
I ask for the ID using the SAME query that returned me NO ID and I get
1233,
the ID of the first insert.
I have tested this as many ways as I can think. The insert is NOT a
delayed
insert. I have tried spacing out the tests. I have checked the DB
using
MySQLCC AND using the query that my test prints to screen and the first
insert is always happening. Below are the actual test results from
run#2.
// Query to see if the data exists.
SELECT ID from Rules where pat = 'AUTO\\_DOGGY';
Identity is -1 because no rows exist.
AUTO_DOGGY Identity was -1
// Insert the rule
Inserting new rule.AUTO_DOGGY
// test
Testing for AUTO_DOGGY
// Same query as above
SELECT ID from Rules where pat = 'AUTO\\_DOGGY';
// Below LIID = Last_Insert_ID()
// Query ID is what is returned from query
LIID = 124566 Query ID = 124565
HELP!
God Bless
Paul C. McNeil
Developer in Java, MS-SQL, MySQL, and web technologies.
GOD BLESS AMERICA!
To God Be The Glory!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBF to MySQL
At 20:55 -0700 7/6/04, John Mistler wrote: For some reason, the imported information showed up as garbled nonsense. The file I was importing was an .xls file. Do you know if there is another TERMINATED BY I should be using? If not, I wonder how I can find out? LOAD DATA will not, I think, read Excel spreadsheets that are in native format. You'll need to extract the data in some form that LOAD DATA can use. There are modules available for this task for various programming languages. There is one for Perl, for example. (If you get the recipes distribution from http://www.kitebird.com/mysql-cookbook/ and look in the transfer directory, you'll find a from_excel.pl script that you can use to yank out the data in tab-delimited format.) The other question I have is: do I have to create a table within the MySQL database with exactly the right number of columns ahead of time for the import to work? - this is what I did. If so, is there a way to import info from a .dbf or .xls file without knowing the structure of the table ahead of time? The aforementioned recipes distribution has a guesstable.pl script (also in the transfer directory) that might be useful for this. It requires input in tab-delimited format, reads the information, and displays a CREATE TABLE statement that fits the data. Thanks, John on 7/6/04 3:59 PM, Chinchilla Zúñiga, Guillermo at [EMAIL PROTECTED] wrote: Try, for example: LOAD DATA INFILE 'myfile.cdf' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'; -Mensaje original- De: John Mistler [mailto:[EMAIL PROTECTED] Enviado el: Martes, 06 de Julio de 2004 04:51 p.m. Para: [EMAIL PROTECTED] Asunto: DBF to MySQL I am wanting to parse the info in a .dbf file (or .xls file for that matter) and place it in a table in a MySQL database. Is this something that I can do with the server side MySQL application, or do I need to figure out a way to do it on the client side? Any description of the method would be very welcome! For what it is worth, I am a Mac OSX.3 user. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
Jeremy Zawodny [EMAIL PROTECTED] wrote: So I haven't really done much to optimize things, as this seems like a fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux Threads). Reliability and performance is not what you should expect to find in FreeBSD 4.x. MySQL doesn't perform well on it because of threads problems in OS itself. We're happily running MySQL on FreeBSD 4.x w/LinuxThreads at Yahoo. So I'm not sure how you back that claim. http://jeremy.zawodny.com/blog/archives/000203.html and so on. :-) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
Charles Sprickman [EMAIL PROTECTED] wrote: The hardware is a dual Athlon MP-1600 smp box with 1GB of RAM. BTW, user-level threads don't scale on more than one CPU. I.e. different threads cannot run on different CPUs on FreeBSD 4. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very strange database behavior
Thanks for the response.
There is currently no primary key on the field so I am relying on the data
being there, or not being there, to decide whether it is safe to insert the
new data.
Through MySQLCC, have_query_cache = YES. Is this the variable I am looking
for? What affect would this have?
God Bless
Paul C. McNeil
Developer in Java, MS-SQL, MySQL, and web technologies.
GOD BLESS AMERICA!
To God Be The Glory!
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 07, 2004 11:49 AM
To: 'Paul McNeil '; 'MySQL General '
Subject: RE: Very strange database behavior
Are you saying that you are not receiving an Unique key violation when you
enter in duplicate data or you are relying on a value being present? If you
are using the later, do you have query caching turned on?
-Original Message-
From: Paul McNeil
To: MySQL General
Sent: 7/7/04 10:43 AM
Subject: Very strange database behavior
Good morning to all.
I have a very strange problem in my database and am trying to track down
whether it is a JAVA problem or a mySQL problem.
I have a table that is supposed to accept only unique data, however, at
this
time, the PK for that information is set to the auto incrementing ID
field.
The unique field is called 'pat'. Here is a description of the problem.
I have a class that posts data to the pat field using java
preparedStatements. The statement executes like this
// test that the insert is unique
SELECT ID from table where pat = 'MyValue';
// If that comes back with an ID, no insert is made
// No ID means we insert.
if(NoIdReturned){
INSERT INTO table(pat) Values('MyValue');
// I have added a test here to insure that the
// data was inserted
// I perform the same query as above
// This is not in the production code.
SELECT ID from table where pat = 'MyValue';
// In tests this comes back with a value every time.
// The production code gets the last_insert_id()
Select last_insert_id() as myID;
// In tests this value and the one above always match.
}
OK. Here's the oddity, you would think that if I run this class again
with
the same parameter for MyValue, the flow would be...
test for MyValue..
test returns IDExists
No insert happens.
That's not what is happening. If I run the class...
once - Insert happens
twice - Insert happens
3 times - ID is found and no insert happens.
Oddity #2 - In test 2, the first query returns no ID.
After the insert I ask for last insert ID and get 1234.
I ask for the ID using the SAME query that returned me NO ID and I get
1233,
the ID of the first insert.
I have tested this as many ways as I can think. The insert is NOT a
delayed
insert. I have tried spacing out the tests. I have checked the DB
using
MySQLCC AND using the query that my test prints to screen and the first
insert is always happening. Below are the actual test results from
run#2.
// Query to see if the data exists.
SELECT ID from Rules where pat = 'AUTO\\_DOGGY';
Identity is -1 because no rows exist.
AUTO_DOGGY Identity was -1
// Insert the rule
Inserting new rule.AUTO_DOGGY
// test
Testing for AUTO_DOGGY
// Same query as above
SELECT ID from Rules where pat = 'AUTO\\_DOGGY';
// Below LIID = Last_Insert_ID()
// Query ID is what is returned from query
LIID = 124566 Query ID = 124565
HELP!
God Bless
Paul C. McNeil
Developer in Java, MS-SQL, MySQL, and web technologies.
GOD BLESS AMERICA!
To God Be The Glory!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sock file
michael johnson [EMAIL PROTECTED] wrote: When trying to start mysql on a lx50 I get the following message: Error 2002 Cannot connect to local MySQL server through socket /var/lib/mysql/mysql.sock (111). What can I do to resolve this problem pls? Start MySQL or change the correct path to socket file. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.0.20, InnoDB my.cnf problems
C.F. Scheidecker Antunes [EMAIL PROTECTED] wrote: Can anyone point me out what is going on wrong in this scenario? The .err file in the datadir can give you a clue. Take a look at the end of it. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query an oracle table from a mysql database
Steve et al: If you want to do it right select a programming language such as Java or C++ There are enough good OOA/OOD folks out there that you will be able to pick a good Java or C++ Engineer for the same price as PHP Otherwise you will be adding on Disk Storage when your single threaded app starts exhausting memory (including virtual disk drive space in particular) and computer resources in general Keep me apprised, Martin Gainty (cell) 617-852-7822 (e) [EMAIL PROTECTED] (http)www.laconiadatasystems.com From: Steve Davies [EMAIL PROTECTED] To: 'Alonso, Claudio Fabian ' [EMAIL PROTECTED] CC: ''[EMAIL PROTECTED]' ' [EMAIL PROTECTED] Subject: Re: How to query an oracle table from a mysql database Date: Wed, 07 Jul 2004 17:02:59 +0100 MIME-Version: 1.0 Received: from lists.mysql.com ([213.136.52.31]) by mc6-f16.hotmail.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 7 Jul 2004 09:03:56 -0700 Received: (qmail 31976 invoked by uid 109); 7 Jul 2004 16:01:08 - Received: (qmail 31955 invoked from network); 7 Jul 2004 16:01:08 - Received: neutral (lists.mysql.com: local policy) X-Message-Info: JGTYoYF78jHJy6039BA9f6fM4wGqMq15 Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Archive: http://lists.mysql.com/mysql/168573 Delivered-To: mailing list [EMAIL PROTECTED] Message-ID: [EMAIL PROTECTED] User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en References: [EMAIL PROTECTED] In-Reply-To: [EMAIL PROTECTED] X-Virus-Checked: Checked Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 07 Jul 2004 16:04:00.0836 (UTC) FILETIME=[046E2840:01C4643C] Try PHP, it has built in functions to access both DBs HTH Steve Victor Pendleton wrote: You may want to look into using a programming language such as C or Java that communicates between both databases. -Original Message- From: Alonso, Claudio Fabian To: '[EMAIL PROTECTED]' Sent: 7/7/04 8:14 AM Subject: How to query an oracle table from a mysql database Importance: High Good morning, I need a MySQL database to be able to query a table in an Oracle database of a remote host. Is this possible? Could anybody tell me how to do it or where to find this information? Thanks in advance, --Claudio .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Check out the latest news, polls and tools in the MSN 2004 Election Guide! http://special.msn.com/msn/election2004.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to query an oracle table from a mysql database
Hello Steve, hello Victor, Thanks for your answers. My PHP application needs to see this Oracle table, but as far as I could see PHP has problems with Oracle 9.2.0. I got a conection problem and found in the PHP's bug database that it isn't currently working. As I'm familiar with PHP/MySQL, I'm trying to see the Oracle table through MySQL. That's why I'm thinking on a way to create in MySQL a view (or something similar) that refers to an external database table (in this case, Oracle). I don't know if this kind of solution is possible, using only MySQL to see a remote Oracle table, not including any programming language. --Claudio .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Increasing number of sockets
Robert Canary [EMAIL PROTECTED] wrote: I am getting an error on my radius server asking me to increase the number of sockets on mysql. Probably you mean connections. Enlarge the value of max_connections variable. You can write it in the /etc/my.cnf in [mysqld] section: [mysqld] max_connections=300 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Drivers to use with VB6
I am using VB6 and ADO in conjunction with ODBC inorder to connect to a MySQL database. This is seems to be considerably slowing down my application. Is there anyother mode of connecting besides ODBC - does anybody have any experience with MyVBQL.dll or MYOLEDB - if so, please share your experiences. Thanks Greg _ Get tips for maintaining your PC, notebook accessories and reviews in Technology 101. http://special.msn.com/tech/technology101.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE-strange DB behavior
Thanks to all who posted help. I found my error, in my code. Sad sad pumpkin. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY vs DISTINCT - questions
Q1: GROUP BY is the command that requests the SQL engine to aggregate sets of rows based on values in common between those rows. Those columns participating in the query that are NOT part of the aggregation key (the key is composed of the columns specified in the GROUP BY clause) must have some sort of function applied to them: COUNT(), SUM(), AVG(), MAX(), MIN(), etc. in order for the aggregation to make sense. DISTINCT is a keyword that indicates you desire only unique values. It's exact effect will depend on what kind of clause it is modifying. Q2: I do not believe that the GROUP BY *always* uses a temporary file. I believe that if your memory is large enough and your engine is tuned appropriately then more of your GROUP BY calculations will be performed in memory rather than on disk. Q3: The LIMIT clause of a select statement is applied to the results of the GROUP BY aggregation and not to the data being aggregated. In fact, I would guess that the difference in the two times would be only that amount of time required to transmit the records beyond what the LIMIT clause allowed from the server to your client. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+ | | Lorderon | | | [EMAIL PROTECTED]| | | net.il | | || | | 07/06/2004 12:21 | | | AM | | || |-+ | | | | To: [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: GROUP BY vs DISTINCT - questions | | Hi, 1. What is the difference between GROUP BY and DISTINCT in the background engine? How MySQL treats each one of them? 2. Why GROUP BY statement ALWAYS uses a temporary file??? 3. Why using LIMIT with a GROUP BY statement takes about the same time as without using LIMIT? any answer would be appriciated... -thanks, Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql sql question
Bruce, It also depends on where the data is coming FROM as to what your options are. Is the source data coming from another table (like, maybe from a bulk import?) or from some kind of user input? You CAN write it in one statement but I need to know your data source. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+-- | | Peter Brawley| | | [EMAIL PROTECTED]| | | ftware.com| | | | | | 07/04/2004 06:03 PM| | | | |-+-- | | | | To: [EMAIL PROTECTED], [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: Re: mysql sql question | | Bruce, i have two hypothetical tables create table owner ( - name char(20) , - ownerid int(10) auto_increment primary key); create table dog ( - name char(20) , - ownerid int(10), - dogid int(10) auto_increment primary key); i'm curious as to how i'd go about inserting a name and the id of the owner, in table dog, in a single sql statement. something like this psuedo sql.. insert table (name, ownerid) values ($name, $ownerid) where owner.owner = owner INSERT INTO takes a single table arg, so to insert int o2 tables,you need editable Views (not yet in MySQL) or at least Stored Procedures (MySQL version 5) on the server side, or you can, as you suggest, do it in the app. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Newbie: Help requested...password hash should be a 16 digit hexadecimal number
I am using Mysql on RH Linux 9. I am getting the following error. Command and result are given below alongwith \s output for diagnostic purposes. I have created a dns database and am trying to create a user mysql user dns with all privileges on it. mysql use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql GRANT ALL PRIVILEGES ON dns.* TO [EMAIL PROTECTED] IDENTIFIED BY PASSWORD dns; ERROR 1133: Password hash should be a 16-digit hexadecimal number mysql \s -- mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386) Connection id: 3 Current database: mysql Current user: [EMAIL PROTECTED] Current pager: stdout Using outfile: '' Server version: 3.23.58 Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 11 hours 8 min 57 sec Threads: 1 Questions: 27 Slow queries: 0 Opens: 22 Flush tables: 1 Open tables: 16 Queries per second avg: 0.001 Help please..I am not only a mySQL newbie but a SQL/DB newbie as well. With regards. Sanjay. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Session wait_timeout and interactive_timeout variables
Do the following commands, when run from an already-established connection, actually have any bearing on anything? SET SESSION wait_timeout=10; SET SESSION interactive_timeout=10; I am working on an application using the C API that needs to lock tables while doing updates. This works fine except for in the rare case when the computer the application is running on loses connectivity while a table is locked. Normally this wouldn't be a huge deal, but I am concerned because the application will mostly be running over a wireless connection which is of course subject to loss of signal. So if one person running the application loses his signal, no one else will be able to do anything. The problem I'm running into is this - I'm testing out this situation by running the mysql command-line client on one computer, write-locking a table, starting a select query from that table on another machine, and turning off the wireless to the first machine. The second machine continues to hang as the mysql server doesn't kill the connection to the first machine and thus its lock is still in effect. Now I believe that wait_timeout and/or interactive_timeout are what I need to help me out by killing such dead threads and thus setting a maximum time that others could hang. This is all well and great, but I am not the admin of the server, so I can't set them globally and the default values of 8 hours are obviously unreasonable for me. In the testing sequence described above, I tried running SET SESSION wait_timeout=10; SET SESSION interactive_timeout=10; on the first machine before acquiring the lock and turning off wireless, but it seemed to have no effect as the second machine continued to hang for the seven minutes I let it sit. After setting the session variables I tried checking them with a select statement, and they showed 10 seconds like they should. It just seems like the server doesn't actually honor them. Is there a problem with the server here, or am I just misunderstanding the way session variables are intended to work? Or is the way I am simulating this case flawed? I must say that the documentation is not very verbose about server variables and so wasn't extremely helpful to me in solving this problem. If there's another better way to solve my locking issue, I would love to hear that as well. Thanks, Aaron Jacobs [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Script to purge
Hi, I would like write a script to purge every nigth the master log with a cron process. I need do it every nigth after load a lot of data into the master. I know if i do 'show master status' i can see the file_name of the last log file and then do -purge master logs to 'file_name'-. Is possible put the file name into a variable and then do - purge master logs to '$variable' -, into a script?. Someone have a example?. I can't use 'PURGE MASTER LOGS BEFORE ' because I use the 4.0.20 version. :-( TIA Luis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving records between tables?
darren wrote: Hi all, I have 2 tables...one for keeping active items while the other is for older records. A housekeep program will come in every night to move some records (matching several criteria from the active table to the history one. I am currently doing SELECT, INSERT and then DELETE. i.e. a select * from active where key=key_value limit 1 and then a insert into history... and then a delete * from active where pri_key='pri_key_value'... I am sure there's a better way right?? I would do this: UPDATE mytable SET archiveflag=1 where sometests; INSERT INTO archtable SELECT FROM mytable where archiveflag=1; T1= SELECT COUNT(*) from mytable where archiveflag=1; T2=SELECT COUNT(*) from archtable where archiveflag=1; if ( T! == T2 ) delete from mytable where archiveflag=1; update archtable set archiveflag=0; else findout what went wrong and fix it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connect to other database format
Elmar, The reason you could not find any documentation about how MySQL connects to other databases is because MySQL does not connect to other databases (except in the special case of replication between MySQL servers). What you have is a program, Excel, contacting a database through an ODBC driver. Excel, in this case, is acting as your GUI to the data. Let me see if I understand your situation correctly: a) You have a program on your Palm that tracks repair jobs in a HanDBase 3.0 database. b) You would like to be able to analyze that information using the MySQL query engine. Before you can use MySQL to analyze any data, that data must be exist in MySQL. You should probably create a new database in MySQL to house your repair job information. Then you will need to populate that database with the data from your other database. I don't know about anyone else on the list but I do not have enough information to be more specific. There may be a utility program available that could automate the entire migration but I can't think of any. One of the most basic, but sometimes the hardest to get just right, techniques in data migration is to export (dump) all of the data from one database system to a text file then re-import that data into the new system. You may have to dump each table to a separate file. You will probably need to hand-create your destination tables in MySQL in order to have an appropriate location to store the incoming data. You may need to adjust the output and input settings (quoted identifiers, field boundaries, row boundaries, etc.) several times before you get a clean transfer. You could write a script that reads data from your HanDBase database (through its specific ODBC driver) and copies that data into a MySQL database (through the MySQL ODBC driver) but I don't know how good a developer you are. I am sorry I could not be more help but migrations like these are typically quite messy. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+ | | Elmar and| | | Madeleine von| | | Muralt | | | [EMAIL PROTECTED]| | | .com.au | | || | | 07/06/2004 08:02 | | | PM | | || |-+ | | | | To: [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: Connect to other database format | | Hi, I'm completely new to MySQL and some of its concepts, therefore, these questions may be very basic. To connect to another database format I need the ODBC driver for that other format - right? I've built a repair job entry database on my Palm with HanDBase 3.0. I also got the HanDBase ODBC Driver v3.0. I tried to connect my desktop database program Paradox to it but there are some issues which seem to be unsolvable. I tried to connect to the .pdb (HanDBase) tables with Excel - that worked. Presumably, this indicates that the HanDBase ODBC driver is doing its job. What I would like to do is to copy the .pdb tables into MySQL tables and then work with these MySQL tables on the desktop. I searched hi and lo but all the connection related info seems to be about other database programs connecting to MySQL but I could not find any info about how MySQL connects to other database formats. Thanks for any help with this. Elmar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql Newbie: Help requested...password hash should be a 16 digithexadecimal number
Sanjay Arora mailto:[EMAIL PROTECTED] on Wednesday, July 07, 2004 10:04 AM said: I am using Mysql on RH Linux 9. I am getting the following error. [snip] Database changed mysql GRANT ALL PRIVILEGES ON dns.* TO [EMAIL PROTECTED] IDENTIFIED BY PASSWORD dns; ERROR 1133: Password hash should be a 16-digit hexadecimal number copy and paste the error into google and try following the first link or click i'm feeling lucky. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Space is filling up
What about getting a bigger drive? Asif Iqbal wrote: Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Script to purge
We delete all binlogs over 7 days old. We figure that if a slave gets that far behind, it is easier and faster to restore last nights master backup and edit the master.info file on the slave to point to todays log file. Luis Mediero wrote: Hi, I would like write a script to purge every nigth the master log with a cron process. I need do it every nigth after load a lot of data into the master. I know if i do 'show master status' i can see the file_name of the last log file and then do -purge master logs to 'file_name'-. Is possible put the file name into a variable and then do - purge master logs to '$variable' -, into a script?. Someone have a example?. I can't use 'PURGE MASTER LOGS BEFORE ' because I use the 4.0.20 version. :-( TIA Luis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Script to purge
On Wed, Jul 07, 2004 at 07:42:46PM +0200, Luis Mediero wrote: Hi, I would like write a script to purge every nigth the master log with a cron process. I need do it every nigth after load a lot of data into the master. I know if i do 'show master status' i can see the file_name of the last log file and then do -purge master logs to 'file_name'-. Is possible put the file name into a variable and then do - purge master logs to '$variable' -, into a script?. Someone have a example?. I can't use 'PURGE MASTER LOGS BEFORE ' because I use the 4.0.20 version. :-( Chapter 7 of High Performance MySQL covers this. And it's even availble free on-line: http://dev.mysql.com/books/hpmysql-excerpts/ch07.html You'll find Perl script that does that. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Space is filling up
gerald_clark wrote: What about getting a bigger drive? I guess that would be my only option eh? Asif Iqbal wrote: Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update query question
Have you tried this other way of making an inner join? UPDATE products_categories AS pc INNER JOIN products AS p ON pc.prod_id = p.id SET pc.prod_sequential_id = p.id But that does not seem right our you could say: UPDATE products_categories AS pc SET pc.prod_sequential_id = pc.prod_id and have the same statement. I think this is what you meant to say: UPDATE products_categories AS pc INNER JOIN products AS p ON pc.prod_id = p.id SET pc.prod_sequential_id = p.sequential_id Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+ | | Chris W. Parker| | | [EMAIL PROTECTED]| | | .com| | || | | 07/06/2004 01:14 | | | PM | | || |-+ | | | | To: [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: update query question | | hello, i've had to change some of the tables in my db to accomodate some greater flexibility in the application that uses it and because of this i need to go through and update all the records. i've done one table by hand and it had about 100 records and took about 20 minutes. but this next table has about 550 records and i really don't feel like doing this all by hand. i'm using MySQL Control Center to do this editing so i'd like to know if there's a single SQL statement i could use to update all the rows. here is a simple representation: products: (pay no attention to the poor choice in column names. this is a retrofitting and will be fixed in later versions.) +--+---+ | id | sequential_id | +--+---+ | PRDX-41 | 1 | | ABCX-01 | 2 | | FF00-11 | 3 | \/\/\/\/\/\/\/\/ | ETC0-99 | 500 | +--+---+ the 'prod_sequential_id' column was added later to the products_categories table. products_categories: +-+-+++ | id | prod_id | prod_sequential_id | cat_id | +-+-+++ | 1 | PRDX-41 | 0 | 41 | | 2 | PRDX-41 | 0 | 15 | | 3 | ABCX-01 | 0 | 13 | | 4 | FF00-11 | 0 | 89 | \/\/\/\/\/\/\/\/ | 610 | ETC0-99 | 0 | 41 | +-+-+++ so... as you can see, prod_sequential_id has all 0's in its column. it should contain the value of products.sequential_id WHERE products_categories.prod_id = products.id. the problem is that i'm not sure how to do this all in one statement (or if it's even possible): (i know the following does not work, but it's basically the logic i think i need.) UPDATE products_categories AS pc, products AS p SET pc.prod_sequential_id = p.id WHERE pc.prod_id = p.id; thanks for your help. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to query an oracle table from a mysql database
No, that isn't possible using mySQL. Try linking PHP with older client libraries (9.0.1, 8.1.5, etc) instead of the newer 9.2 libraries and see if that fixes your problem with PHP. You can download them from otn.oracle.com swany --- Alonso, Claudio Fabian [EMAIL PROTECTED] wrote: Hello Steve, hello Victor, Thanks for your answers. My PHP application needs to see this Oracle table, but as far as I could see PHP has problems with Oracle 9.2.0. I got a conection problem and found in the PHP's bug database that it isn't currently working. As I'm familiar with PHP/MySQL, I'm trying to see the Oracle table through MySQL. That's why I'm thinking on a way to create in MySQL a view (or something similar) that refers to an external database table (in this case, Oracle). I don't know if this kind of solution is possible, using only MySQL to see a remote Oracle table, not including any programming language. --Claudio .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: odd problem
I got back from vacation today and I didn't see that anyone else has
responded yet so I figure that later is better than never ;-)
Your INSERT speed will be directly related to how fast all of your JOINS
can be accomplished. Your JOIN speed is related to two things: the size of
your tables and your index structures.
Could you post the SHOW INDEX FROM results for: url_servers, url_paths,
url_queries, user_agents, and url_visit? Could you also post the results
of:
EXPLAIN SELECT bt.user, bt.time, bt.ip, uv.urlid, bt.timetaken,
bt.cs_size, bt.sc_size, bt.method_ID, bt.action_ID, bt.virus, ua.ID
FROM bulk_table bt
INNER JOIN url_servers us ON us.server=bt.server
INNER JOIN url_paths up ON up.path=bt.path
INNER JOIN url_queries uq ON uq.query=bt.query
INNER JOIN user_agents ua ON ua.useragent=bt.useragent
INNER JOIN url_visit uv ON uv.url_server_ID=us.ID
AND uv.url_path_ID=up.ID
AND uv.url_scheme_ID=bt.scheme_ID
AND uv.url_query_ID=uq.ID };
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
|-+
| | J S|
| | [EMAIL PROTECTED]|
| | com |
| ||
| | 07/01/2004 11:22 |
| | AM |
| ||
|-+
|
|
|
| To: [EMAIL PROTECTED]
|
| cc:
|
| Fax to:
|
| Subject: odd problem
|
|
Hi,
The code in my function below (insert_internet_usage) has suddenly
dramatically slowed down and I don't understand why. The logs I'm parsing
are the same size as before, but what used to take 15 minutes has shot up
to
about 4 hours. It's almost as if the indexes had stopped working.
I wonder if someone here can help me out please? I've tried to include all
the information which I thought might be relevant below
but if you need more let me know. There haven't been any errors reported in
the log since I restarted mysql. Here's my script log:
Thu Jul 1 08:56:18 2004: PARSING
/sawmill/rawlog/SG_CSGL02_main_47061221.log.gz ...
Thu Jul 1 09:00:37 2004: BULK_TABLE_INSERT ...
Thu Jul 1 09:01:43 2004: INSERT_URL_SERVERS ...
Thu Jul 1 09:02:26 2004: INSERT_URL_PATHS ...
Thu Jul 1 09:03:54 2004: INSERT_URL_QUERIES ...
Thu Jul 1 09:04:32 2004: INSERT_USER_AGENTS ...
Thu Jul 1 09:05:09 2004: INSERT_URL_VISITS ...
Thu Jul 1 09:10:06 2004: INSERT_INTERNET_USAGE ...
Thu Jul 1 09:19:31 2004: DELETE_BULK_TABLE ...
Thu Jul 1 09:19:32 2004: PARSING
/sawmill/rawlog/SG_CSGL02_main_47061121.log.gz ...
Thu Jul 1 09:29:02 2004: BULK_TABLE_INSERT ...
Thu Jul 1 09:31:36 2004: INSERT_URL_SERVERS ...
Thu Jul 1 09:33:21 2004: INSERT_URL_PATHS ...
Thu Jul 1 09:36:26 2004: INSERT_URL_QUERIES ...
Thu Jul 1 09:38:07 2004: INSERT_USER_AGENTS ...
Thu Jul 1 09:39:37 2004: INSERT_URL_VISITS ...
=== This is where the
time difference has
Thu Jul 1 09:54:19 2004: INSERT_INTERNET_USAGE ...
=== suddenly
increased
Thu Jul 1 13:28:37 2004: DELETE_BULK_TABLE ...
Thu Jul 1 13:28:38 2004: PARSING
/sawmill/rawlog/SG_CSGL02_main_47061021.log.gz ...
Thu Jul 1 13:39:09 2004: BULK_TABLE_INSERT ...
Thu Jul 1 13:41:59 2004: INSERT_URL_SERVERS ...
Thu Jul 1 13:43:52 2004: INSERT_URL_PATHS ...
Thu Jul 1 13:47:11 2004: INSERT_URL_QUERIES ...
Thu Jul 1 13:48:55 2004: INSERT_USER_AGENTS ...
Thu Jul 1 13:50:35 2004: INSERT_URL_VISITS ...
Thu Jul 1 14:06:55 2004: INSERT_INTERNET_USAGE ...
time now is 16:13 and it's still going.
This is the function:
sub insert_internet_usage(){ my $sql = qq {INSERT internet_usage
(uid,time,ip,urlid,timetaken,
cs_size,sc_size,method_ID,action_ID,virus_ID,userag
ent_ID)
SELECT bt.user, bt.time, bt.ip, uv.urlid, bt.timetaken,
bt.cs_size, bt.sc_size, bt.method_ID, bt.action_ID, b
t.virus, ua.ID
FROM bulk_table bt
INNER JOIN url_servers us ON us.server=bt.server
INNER JOIN url_paths up ON up.path=bt.path
RE: How to query an oracle table from a mysql database
At 13:18 -0300 7/7/04, Alonso, Claudio Fabian wrote: Hello Steve, hello Victor, Thanks for your answers. My PHP application needs to see this Oracle table, but as far as I could see PHP has problems with Oracle 9.2.0. I got a conection problem and found in the PHP's bug database that it isn't currently working. As I'm familiar with PHP/MySQL, I'm trying to see the Oracle table through MySQL. That's why I'm thinking on a way to create in MySQL a view (or something similar) that refers to an external database table (in this case, Oracle). I don't know if this kind of solution is possible, using only MySQL to see a remote Oracle table, not including any programming language. It's not. You must use a program as a bridge, as people have been suggesting. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unexpected behaviour: SELECT with OR returns empty set
This sounds like a shortcut optimization in the JOIN processor. Whenever you write FROM tableA, tableB ... WHERE tableA.keyfield = tableB.foreignkeyfield it has the same effect as writing FROM tableA INNER JOIN tableB ON tableA.keyfield = tableB.foreignkeyfield. The engine handles the comma separator in the FROM clause as an implicit INNER JOIN. Since the second table had no rows, there was no way for an INNER JOIN to actually work (no possible matches) so there was no need for the engine to go through the effort of creating a Cartesian product. When you added a row to oems, now the engine had to create that cartesian product of the two tables so that the rest of the WHERE statement could be evaluated. AND since you specified an OR condition you luck-out and get your 4 rows back. I am 99.99% certain that if you add 3 more rows of data to oems, you will have 16 rows as your result. You should see each batch of the original 4 rows repeated once for each row of data you have in oems What exactly was the question you wanted answered with the query SELECT * FROM customers,oems WHERE ((customers.companyID= 1509 ) OR (oems.companyID) = 1509) ? You may have wanted to write that as a UNION query or as a LEFT JOIN to get the correct response. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+ | | Dave Gibson | | | [EMAIL PROTECTED]| | | | | || | | 07/02/2004 08:32 | | | AM | | || |-+ | | | | To: [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: Unexpected behaviour: SELECT with OR returns empty set | | Hi, I'm getting some unexpected behaviour from a query and was hoping someone could shed some light on whether it's a user error or something else. I have two tables: customers, oems which both have a companyID field (marked as a foreign key). When I do: SELECT * FROM customers WHERE customers.companyID = 1509 I get 4 rows returned. However, when I do: SELECT * FROM customers,oems WHERE ((customers.companyID = 1509 ) OR (oems.companyID) = 1509) I get 0 rows matched. Investigation showed that the oems table contains no rows. If I add a rown to it, even one that doesn't match the query above, I get the correct result. ring any bells with anyone? Thanks, Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Space is filling up
If you database contains time-based data you could age out old records. I only need to keep data for 6 months so I run a nightly script to delete any records more than 6 months old. And before anyone asks...yes, I also run another script to ANALYZE/OPTIMIZE my tables. Jack -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 2:07 PM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: Space is filling up gerald_clark wrote: What about getting a bigger drive? I guess that would be my only option eh? Asif Iqbal wrote: Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Wed, Jul 07, 2004 at 07:11:17PM +0300, Egor Egorov wrote: Jeremy Zawodny [EMAIL PROTECTED] wrote: So I haven't really done much to optimize things, as this seems like a fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux Threads). Reliability and performance is not what you should expect to find in FreeBSD 4.x. MySQL doesn't perform well on it because of threads problems in OS itself. We're happily running MySQL on FreeBSD 4.x w/LinuxThreads at Yahoo. So I'm not sure how you back that claim. http://jeremy.zawodny.com/blog/archives/000203.html and so on. :-) Assuming one follows the *bold* UPDATE link at the top, I still don't see it... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Increasing number of sockets
Gee, how about just a little more coffee... ;-)
I meant, {long version}...
It could be the radius error is reflecting its
failure to connect to the database because the
database has no connections available. I found
the max_connection variable (default is 90) and
applied it to the safe_mysqld section in the my.cn
it seemed to have did the trick, I am not getting
the error anymore.
Paul DuBois wrote:
At 6:47 -0500 7/7/04, Robert Canary wrote:
Unless it the error is referring to the number of simultaneous
connections. Which defualts to 90 (max_connection under safe_mysql).
I'm sorry, I'm unable to parse those sentences...
TCP/IP of course
Paul DuBois wrote:
At 22:09 -0500 7/6/04, Robert Canary wrote:
I am getting an error on my radius server asking me to increase the
number of sockets on mysql.
What kind of socket? TCP/IP socket? Unix domain socket file?
Either way, that sounds like an odd message. The server listens to
a single TCP/IP port and a single Unix socket file. There's no need
for more.
I'm looking the man pages for safe_mysqld and mysql but I don't see
anything that offers to provision this.
How does increase the number of sockets on the mysql?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving records between tables?
LOCK TABLE active_table WRITE, history_table WRITE; #assuming the columns in the tables are exactly #the same insert into history_table select * from active_table; delete from active_table; UNLOCK TABLES; if the columns aren't the same between the tables then you need to do something like insert into history_table (colA, colB, colC,...) select (col1, col2, col3, ...) from active_table; Hope that helps, Swany --- darren [EMAIL PROTECTED] wrote: Hi all, I have 2 tables...one for keeping active items while the other is for older records. A housekeep program will come in every night to move some records (matching several criteria from the active table to the history one. I am currently doing SELECT, INSERT and then DELETE. i.e. a select * from active where key=key_value limit 1 and then a insert into history... and then a delete * from active where pri_key='pri_key_value'... I am sure there's a better way right?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: update query question
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] on Wednesday, July 07, 2004 11:08 AM said: Have you tried this other way of making an inner join? no i did not because i did know you could do a JOIN on an UPDATE. thanks for your suggestions i will try them out. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to query an oracle table from a mysql database
A custom MySQL UDF is one solution if you are comfortable with C. Off list question: What sort of `problem` does PHP have with Oracle 9i that is prohibiting you? -Original Message- From: Alonso, Claudio Fabian To: 'Steve Davies' Cc: ''[EMAIL PROTECTED]' ' Sent: 7/7/04 11:18 AM Subject: RE: How to query an oracle table from a mysql database Importance: High Hello Steve, hello Victor, Thanks for your answers. My PHP application needs to see this Oracle table, but as far as I could see PHP has problems with Oracle 9.2.0. I got a conection problem and found in the PHP's bug database that it isn't currently working. As I'm familiar with PHP/MySQL, I'm trying to see the Oracle table through MySQL. That's why I'm thinking on a way to create in MySQL a view (or something similar) that refers to an external database table (in this case, Oracle). I don't know if this kind of solution is possible, using only MySQL to see a remote Oracle table, not including any programming language. --Claudio .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Drivers to use with VB6
Are you using a DSN or DSN-less? Have you checked out mysqlvb.com? -Original Message- From: Greg Zimmermack To: [EMAIL PROTECTED] Sent: 7/7/04 11:27 AM Subject: Drivers to use with VB6 I am using VB6 and ADO in conjunction with ODBC inorder to connect to a MySQL database. This is seems to be considerably slowing down my application. Is there anyother mode of connecting besides ODBC - does anybody have any experience with MyVBQL.dll or MYOLEDB - if so, please share your experiences. Thanks Greg _ Get tips for maintaining your PC, notebook accessories and reviews in Technology 101. http://special.msn.com/tech/technology101.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Newbie: Help requested...password hash should be a 16 digit hexadecimal number
At 22:34 +0530 7/7/04, Sanjay Arora wrote: I am using Mysql on RH Linux 9. I am getting the following error. Command and result are given below alongwith \s output for diagnostic purposes. I have created a dns database and am trying to create a user mysql user dns with all privileges on it. mysql use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql GRANT ALL PRIVILEGES ON dns.* TO [EMAIL PROTECTED] IDENTIFIED BY PASSWORD dns; ERROR 1133: Password hash should be a 16-digit hexadecimal number OMIT the word PASSWORD. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Space is filling up
--- Asif Iqbal [EMAIL PROTECTED] wrote: gerald_clark wrote: What about getting a bigger drive? I guess that would be my only option eh? If any of your data can be considered history data that is never modified, you could create compressed myISAM tables for that data, removing it from the inno tablespaces. This could give you a little breathing room until you can get a bigger drive. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to query an oracle table from a mysql database
Hello Victor, For your off-topic question, my problem is the same as the one described here: http://bugs.php.net/bug.php?id=28680 I can't access using 9.2. They say 9.2 is too new for current PHP versions. I'll install an older version of the Oracle client and try again. If I can't do it I'll be asking for additional ideas :) (I'm pretty comforable with C but I'm not sure what a MySQL UDF is, I've got to find it out). Thanks a lot, --Claudio -Mensaje original- De: Victor Pendleton [mailto:[EMAIL PROTECTED] Enviado el: miércoles 7 de julio de 2004 15:46 Para: 'Alonso, Claudio Fabian '; ''Steve Davies' ' CC: '''[EMAIL PROTECTED]' ' ' Asunto: RE: How to query an oracle table from a mysql database A custom MySQL UDF is one solution if you are comfortable with C. Off list question: What sort of `problem` does PHP have with Oracle 9i that is prohibiting you? -Original Message- From: Alonso, Claudio Fabian To: 'Steve Davies' Cc: ''[EMAIL PROTECTED]' ' Sent: 7/7/04 11:18 AM Subject: RE: How to query an oracle table from a mysql database Importance: High Hello Steve, hello Victor, Thanks for your answers. My PHP application needs to see this Oracle table, but as far as I could see PHP has problems with Oracle 9.2.0. I got a conection problem and found in the PHP's bug database that it isn't currently working. As I'm familiar with PHP/MySQL, I'm trying to see the Oracle table through MySQL. That's why I'm thinking on a way to create in MySQL a view (or something similar) that refers to an external database table (in this case, Oracle). I don't know if this kind of solution is possible, using only MySQL to see a remote Oracle table, not including any programming language. --Claudio .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Increasing number of sockets
At 13:41 -0500 7/7/04, Robert Canary wrote:
Gee, how about just a little more coffee... ;-)
I meant, {long version}...
It could be the radius error is reflecting its failure to connect to
the database because the database has no connections available. I
found the max_connection variable (default is 90) and applied it to
the safe_mysqld section in the my.cn it seemed to have did the
trick, I am not getting the error anymore.
Okay. Then the error really was about increasing the number of available
connections (not sockets), and setting max_connections is the right thing
to do.
Paul DuBois wrote:
At 6:47 -0500 7/7/04, Robert Canary wrote:
Unless it the error is referring to the number of simultaneous
connections. Which defualts to 90 (max_connection under
safe_mysql).
I'm sorry, I'm unable to parse those sentences...
TCP/IP of course
Paul DuBois wrote:
At 22:09 -0500 7/6/04, Robert Canary wrote:
I am getting an error on my radius server asking me to increase
the number of sockets on mysql.
What kind of socket? TCP/IP socket? Unix domain socket file?
Either way, that sounds like an odd message. The server listens to
a single TCP/IP port and a single Unix socket file. There's no need
for more.
I'm looking the man pages for safe_mysqld and mysql but I don't
see anything that offers to provision this.
How does increase the number of sockets on the mysql?
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT DISTINCT?
Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
high priority messages (Re: How to query an oracle table from a mysql database)
Hi all, Please, do NOT send messages to a public mailinglist as high priority by flagging the email itself. You're sending this message to hundreds/thousands of people. It might be important to you, but not for each recipient. If someone has filters/triggers for important email, he will get quite annoyed to find out it's only an emailinglist message. People WILL read and respond to your message with the high priority flag. With regards, Martijn Tonies -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Space is filling up
Jack Coxen wrote: If you database contains time-based data you could age out old records. I only need to keep data for 6 months so I run a nightly script to delete any records more than 6 months old. And before anyone asks...yes, I also run another script to ANALYZE/OPTIMIZE my tables. But does he even have enough disk space to optimize tables? Jack -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 2:07 PM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: Space is filling up gerald_clark wrote: What about getting a bigger drive? I guess that would be my only option eh? Asif Iqbal wrote: Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DISTINCT?
Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DISTINCT?
Create a unique index on each column that you don't want to be duplicated. create UNIQUE index table_u1 on table(some_column) --- John Mistler [EMAIL PROTECTED] wrote: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT DISTINCT?
This is certainly the first step. Also, if you want to insert only those rows which are not already present, you can use something akin to: INSERT INTO table1 SELECT * FROM table2 t2 LEFT JOIN table1 t1 ON (unique row identifiers -- whatever these are for your data) WHERE t1.XYZ IS NULL (obviously put in the appropriate column names etc for your data structure!) Cheers, Matt -Original Message- From: Joshua J. Kugler [mailto:[EMAIL PROTECTED] Sent: 07 July 2004 22:22 To: [EMAIL PROTECTED] Subject: Re: INSERT DISTINCT? Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DISTINCT?
Try insert ignore. Check the manual page for insert syntax. On Wednesday, July 7, 2004, at 05:22 PM, Matt Chatterley wrote: This is certainly the first step. Also, if you want to insert only those rows which are not already present, you can use something akin to: INSERT INTO table1 SELECT * FROM table2 t2 LEFT JOIN table1 t1 ON (unique row identifiers -- whatever these are for your data) WHERE t1.XYZ IS NULL (obviously put in the appropriate column names etc for your data structure!) Cheers, Matt -Original Message- From: Joshua J. Kugler [mailto:[EMAIL PROTECTED] Sent: 07 July 2004 22:22 To: [EMAIL PROTECTED] Subject: Re: INSERT DISTINCT? Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug #1858: Repeatable on 3.53.57
A week ago I posted that we were having horrible problems with MySQL's replication failing and reconnecting (after years of reliable operation). I still haven't come up with a solution to the problems, it just looks like the master keeps tearing down the replication connection, and the slave keeps reconnecting. Setting up more slaves, they exhibit the same exact problems. As noted in my previous emails, after about 4-6 errors like this: 040707 2:24:44 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040707 2:24:44 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.178' position 13468249 040707 2:24:44 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.178' at position 13468249 040707 2:39:57 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040707 2:39:57 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.178' position 25712380 040707 2:39:57 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.178' at position 25712380 The replication would completely stop with this error: 040702 4:13:51 Slave thread exiting, replication stopped in log 'mysql-bin.167' at position 108374685 Well, it turns out the slave disconnecting and reconnecting does not appear to be related to the slave stopping (odd since they both became problems around the same time). It looks like at 4am, our optimize database script fires off on the SLAVE, and it looks like the slave thread dies a little while after. I have turned off nightly optimization, and for the past 5 days, we have not seen the slave stop. So I would believe that we are running into bug: http://bugs.mysql.com/bug.php?id=1858 on the 3.53.57/3.53.58 line of code. The bug was already fixed on the 4.0.x line of code, but presumably a patch needs to be made to the 3.53.x line of code aswell. As for the rest of my replication problems... who knows :) Thanks, -Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBF to MySQL
I'm sure it did...DBF and XLS files are not plain text. What Chincilla gave your was a bit of code for importing CSV files, after they had been exported from Excel. I'm not sure *why* he gave you that code. You can do one of two things. 1) Open up those files in Excel (it will also open DBF files) and export them as CSV, or 2) find a class and programmatically import them (search freshmeat.net for 'dbf'). If you need more info, holler. j- k- On Tuesday 06 July 2004 07:55 pm, John Mistler said something like: For some reason, the imported information showed up as garbled nonsense. The file I was importing was an .xls file. Do you know if there is another TERMINATED BY I should be using? If not, I wonder how I can find out? The other question I have is: do I have to create a table within the MySQL database with exactly the right number of columns ahead of time for the import to work? - this is what I did. If so, is there a way to import info from a .dbf or .xls file without knowing the structure of the table ahead of time? Thanks, John on 7/6/04 3:59 PM, Chinchilla Zúñiga, Guillermo at [EMAIL PROTECTED] wrote: Try, for example: LOAD DATA INFILE 'myfile.cdf' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'; -Mensaje original- De: John Mistler [mailto:[EMAIL PROTECTED] Enviado el: Martes, 06 de Julio de 2004 04:51 p.m. Para: [EMAIL PROTECTED] Asunto: DBF to MySQL I am wanting to parse the info in a .dbf file (or .xls file for that matter) and place it in a table in a MySQL database. Is this something that I can do with the server side MySQL application, or do I need to figure out a way to do it on the client side? Any description of the method would be very welcome! For what it is worth, I am a Mac OSX.3 user. Thanks, John -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting rows that match two criteria
I am trying to figure out what my select statement should be to combine both of these into one: SELECT userid FROM Answers WHERE answer like Education%; SELECT userid FROM Answers WHERE answer=Student; Table Answers looks like: -id int -userid int -answer text for each row there would be something like: +-++--+ | qid | userid | answer | +-++--+ | 5 | 3000 | Student | +-++--+ There are often multiple asnwers for each question. I want to select the userid's from the table where people answered Student in question 5 and Education in question 6. I am using MySQL 4.08 so I can't do the subquery thing. Thanks, Jonathan Duncan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very strange database behavior
Paul,
Has this class worked properly in the past? Have you tried executing
the queries your class executes manually via the mysql client or a
similar application removing your Java code from the equation? Have
you run 'check table' if the table is either MyISAM or InnoDB?
On Jul 7, 2004, at 11:43 AM, Paul McNeil wrote:
Good morning to all.
I have a very strange problem in my database and am trying to track
down
whether it is a JAVA problem or a mySQL problem.
I have a table that is supposed to accept only unique data, however,
at this
time, the PK for that information is set to the auto incrementing ID
field.
The unique field is called 'pat'. Here is a description of the
problem.
I have a class that posts data to the pat field using java
preparedStatements. The statement executes like this
// test that the insert is unique
SELECT ID from table where pat = 'MyValue';
// If that comes back with an ID, no insert is made
// No ID means we insert.
if(NoIdReturned){
INSERT INTO table(pat) Values('MyValue');
// I have added a test here to insure that the
// data was inserted
// I perform the same query as above
// This is not in the production code.
SELECT ID from table where pat = 'MyValue';
// In tests this comes back with a value every time.
// The production code gets the last_insert_id()
Select last_insert_id() as myID;
// In tests this value and the one above always match.
}
OK. Here's the oddity, you would think that if I run this class again
with
the same parameter for MyValue, the flow would be...
test for MyValue..
test returns IDExists
No insert happens.
That's not what is happening. If I run the class...
once - Insert happens
twice - Insert happens
3 times - ID is found and no insert happens.
Oddity #2 - In test 2, the first query returns no ID.
After the insert I ask for last insert ID and get 1234.
I ask for the ID using the SAME query that returned me NO ID and I get
1233,
the ID of the first insert.
I have tested this as many ways as I can think. The insert is NOT a
delayed
insert. I have tried spacing out the tests. I have checked the DB
using
MySQLCC AND using the query that my test prints to screen and the first
insert is always happening. Below are the actual test results from
run#2.
// Query to see if the data exists.
SELECT ID from Rules where pat = 'AUTO\\_DOGGY';
Identity is -1 because no rows exist.
AUTO_DOGGY Identity was -1
// Insert the rule
Inserting new rule.AUTO_DOGGY
// test
Testing for AUTO_DOGGY
// Same query as above
SELECT ID from Rules where pat = 'AUTO\\_DOGGY';
// Below LIID = Last_Insert_ID()
// Query ID is what is returned from query
LIID = 124566 Query ID = 124565
HELP!
God Bless
Paul C. McNeil
Developer in Java, MS-SQL, MySQL, and web technologies.
GOD BLESS AMERICA!
To God Be The Glory!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting rows that match two criteria
Jonathan, I'm going to assume you want to match the answer fields in the way those 2 query examples do and that the qid field refers to the Question number. SELECT userid FROM Answers a1 JOIN Answers a2 ON a1.userid = a2.userid WHERE a1.qid = 5 AND a1.answer = 'Student' AND a2.qid = 6 AND a2.answer LIKE 'Education%'; The above joins the table back to another copy of itself by userid. This allows you to find the students that answered both questions correctly. Give it a go. cheers Lachlan -Original Message- From: Jonathan Duncan [mailto:[EMAIL PROTECTED] Sent: Thursday, 8 July 2004 10:04 AM To: [EMAIL PROTECTED] Subject: selecting rows that match two criteria I am trying to figure out what my select statement should be to combine both of these into one: SELECT userid FROM Answers WHERE answer like Education%; SELECT userid FROM Answers WHERE answer=Student; Table Answers looks like: -id int -userid int -answer text for each row there would be something like: +-++--+ | qid | userid | answer | +-++--+ | 5 | 3000 | Student | +-++--+ There are often multiple asnwers for each question. I want to select the userid's from the table where people answered Student in question 5 and Education in question 6. I am using MySQL 4.08 so I can't do the subquery thing. Thanks, Jonathan Duncan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting rows that match two criteria
You can do it one of two ways.. Either you can do a self join like the following: select t1.userid from answers t1, answers t2 where t1.qid = 5 and lower(t1.answer)='student' and t2.qid = 6 and lower(t2.answer) like 'edu%' and t1.userid = t2.userid or you can use a union (available in 4.0.0 or greater) select userid from answers where qid = 5 and lower(answer) = 'student' UNION select userid from answers where qid = 6 and lower(answer) like 'edu%' union automatically does a distinct so you will get one row back for each user that answered that they are education students. --- Jonathan Duncan [EMAIL PROTECTED] wrote: I am trying to figure out what my select statement should be to combine both of these into one: SELECT userid FROM Answers WHERE answer like Education%; SELECT userid FROM Answers WHERE answer=Student; Table Answers looks like: -id int -userid int -answer text for each row there would be something like: +-++--+ | qid | userid | answer | +-++--+ | 5 | 3000 | Student | +-++--+ There are often multiple asnwers for each question. I want to select the userid's from the table where people answered Student in question 5 and Education in question 6. I am using MySQL 4.08 so I can't do the subquery thing. Thanks, Jonathan Duncan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a OPS$ equivalent in mysql
Hi. an OPS$ account in oracle lets a given user log in automatically to a OPS$user-id account from the local machine without a password. but not from another user-id on the same machine. ie [EMAIL PROTECTED] $ mysql -u ianh would work but [EMAIL PROTECTED] $ mysql -u ianh would not. Does mysql have this functionality? Thanks Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DISTINCT?
I am not certain from the documentation whether it is advisable to create a unique multi-column index on two columns that are already individually indexed. The individual indexes I assume I need for when I do a SELECT on those particular columns. The multi-column one I need for the reasons discussed below. Any one know? Thanks, John on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT DISTINCT?
John, As far as I'm aware there is no problem creating a multi-column unique index on fields that have already been indexed seperately. The multi column index will be used in situations where you are trying to find unique _combinations_ of the two fields and the individual indexes in situations where you are trying to find instances of one OR the other. If you are doing a number of inserts or multi-row inserts you may want to use the IGNORE parameter. This will allow the INSERT command to complete and return successfully, simply skipping the rows where dupes are found, otherwise mysql is going to generate a duplicate key error when duplicates are found. Hope this clarifies some things for you. Lachlan -Original Message- From: John Mistler [mailto:[EMAIL PROTECTED] Sent: Thursday, 8 July 2004 12:27 PM To: [EMAIL PROTECTED] Subject: Re: INSERT DISTINCT? I am not certain from the documentation whether it is advisable to create a unique multi-column index on two columns that are already individually indexed. The individual indexes I assume I need for when I do a SELECT on those particular columns. The multi-column one I need for the reasons discussed below. Any one know? Thanks, John on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More semaphore mystery
Mitch, the answer is the same as the last time: send me the FULL .err log gzipped. Regards, Heikki - Original Message - From: Mitch Pirtle [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, July 06, 2004 3:50 PM Subject: More semaphore mystery Hi gang, Back in the throes of semaphores that refuse to play nice with all the other kids on the playground ;-) I'm getting the following in my error file: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 79461482, signal count 75199550 --Thread 23744585 has waited at btr0cur.c line 401 for 556.00 seconds the semaph ore: X-lock on RW-latch at 0x868f29b0 created in file buf0buf.c line 438 a writer (thread id 23744585) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0cur.c line 401 Last time write locked in file btr0cur.c line 401 ...So what's a guy to do? Is there a way I can determine just what exactly is causing this? -- Mitch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a OPS$ equivalent in mysql
At 11:17 +1000 7/8/04, Ian Holsman wrote: Hi. an OPS$ account in oracle lets a given user log in automatically to a OPS$user-id account from the local machine without a password. but not from another user-id on the same machine. ie [EMAIL PROTECTED] $ mysql -u ianh would work but [EMAIL PROTECTED] $ mysql -u ianh would not. Does mysql have this functionality? If I understand you correctly, no. If an account for a given user on a machine has no password, anyone on that machine can use it. (When you connect to the MySQL server, you specify a username. Anyone could specify the username of the account with no password.) Perhaps an acceptable alternative would be to do this: - Assign the account a password. (All MySQL accounts really should have a password anyway. _ For the user that you want to be able to connect automatically, create a ~/.my.cnf option file that lists the password in the [client] section: [client] password=pass-for-account Make the file readable only by that user. Now when the user runs a MySQL client program, it'll pick up the password from the option file automatically and the user won't have to type it in. If the goal is for the user not to have to type passwords, this procedure would achieve that, and it wouldn't leave the account passwordless. http://dev.mysql.com/doc/mysql/en/Using_MySQL_Programs.html http://dev.mysql.com/doc/mysql/en/Option_files.html http://dev.mysql.com/doc/mysql/en/Connecting.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT DISTINCT?
John,
Here is an example which should clarify for you:
INSERT INTO testTable (a,b) VALUES ('AVAL', 'BVAL');
Let's say testTable looks like this:
+-+
| a | b |
--|
| AVAL | EVAL |
| FVAL | BVAL |
+-+
You have your unique index over (a,b). The insert statement above will
succeed because there is no entry in testTable where _both_ a and b are as
specified in the INSERT.
The table should then look like:
+-+
| a | b |
--|
| AVAL | EVAL |
| FVAL | BVAL |
| AVAL | BVAL |
+-+
Any of the following INSERT statements would now fail:
INSERT INTO testTable (a,b) VALUES ('AVAL', 'BVAL');
INSERT INTO testTable (a,b) VALUES ('AVAL', 'EVAL');
INSERT INTO testTable (a,b) VALUES ('FVAL', 'BVAL');
Hope this helps,
Regards,
Lachlan
PS. Please remember to reply to the [EMAIL PROTECTED] address rather
than my direct address, this way everyone can keep track of the discussion.
-Original Message-
From: John Mistler [mailto:[EMAIL PROTECTED]
Sent: Thursday, 8 July 2004 1:24 PM
To: Lachlan Mulcahy
Subject: Re: INSERT DISTINCT?
Thanks, Lachlan! One other thing I am wondering: given two columns (a,b)
with a UNIQUE multi-column index over them, will an insert fail if a row
with column a's data already exists, and the data for column b does not? I
am wanting to only prevent inserts when BOTH a b's data already exists.
Thanks,
John
on 7/7/04 7:46 PM, Lachlan Mulcahy at [EMAIL PROTECTED] wrote:
John,
As far as I'm aware there is no problem creating a multi-column unique
index
on fields that have already been indexed seperately. The multi column
index
will be used in situations where you are trying to find unique
_combinations_ of the two fields and the individual indexes in situations
where you are trying to find instances of one OR the other.
If you are doing a number of inserts or multi-row inserts you may want to
use the IGNORE parameter. This will allow the INSERT command to complete
and return successfully, simply skipping the rows where dupes are found,
otherwise mysql is going to generate a duplicate key error when duplicates
are found.
Hope this clarifies some things for you.
Lachlan
-Original Message-
From: John Mistler [mailto:[EMAIL PROTECTED]
Sent: Thursday, 8 July 2004 12:27 PM
To: [EMAIL PROTECTED]
Subject: Re: INSERT DISTINCT?
I am not certain from the documentation whether it is advisable to create
a
unique multi-column index on two columns that are already individually
indexed. The individual indexes I assume I need for when I do a SELECT on
those particular columns. The multi-column one I need for the reasons
discussed below. Any one know?
Thanks,
John
on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote:
Certainly, it's called making a unique index on the field(s) you want to
keep
unique.
Hope that helps.
j- k-
On Wednesday 07 July 2004 12:48 pm, John Mistler said something like:
Is there a way to do an INSERT on a table only if no row already exists
with the same info for one or more of the columns as the row to be
inserted? That is, without using a method outside SQL?
Thanks,
John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DISTINCT?
At 19:26 -0700 7/7/04, John Mistler wrote: I am not certain from the documentation whether it is advisable to create a unique multi-column index on two columns that are already individually indexed. The individual indexes I assume I need for when I do a SELECT on those particular columns. The multi-column one I need for the reasons discussed below. Any one know? If you have indexes on column A and column B, then if you create a multiple-column unique index on (A,B), you could remove the index on A. The reason for this is that MySQL can use a leftmost prefix of a multiple-column index as if you had an index on just the leftmost columns. In other words, an index on (A, B) can be use when you search for combinations of A and B, or when you search for just A. You cannot remove the index that you have on just B, because B is not a leftmost index of (A, B). http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html Thanks, John on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqladmin shutdown command hangs
Hello: We are using mysql 4.0.17 on Linux We have installed a mysql database on our our server. The data and log files are stored on an external SCSI disk array which is connected to our server using FC cable connected to SCSI port. The mysql process is running on the local machine but the data and logs are stored on the disk array. When there is an accidental communication breakdown between disk array and server on which mysql process is running (e.g, pulling out FC cable), this is being detected by our scripts and then we are trying to shutdown the mysql process by using mysqladmin shutdown option. However this command hangs and does not complete. We tried the --force option also with the mysqladmin shutdown command, but it still hung. Does anybody have any suggestion/solution. Thanks, Ravi
Re: INSERT DISTINCT?
Paul, What about the case where column A is a foreign key? In that case would you be forced to keep the index on column A or could you use the A,B index since A is the left most prefix? Tripp --- Paul DuBois [EMAIL PROTECTED] wrote: At 19:26 -0700 7/7/04, John Mistler wrote: I am not certain from the documentation whether it is advisable to create a unique multi-column index on two columns that are already individually indexed. The individual indexes I assume I need for when I do a SELECT on those particular columns. The multi-column one I need for the reasons discussed below. Any one know? If you have indexes on column A and column B, then if you create a multiple-column unique index on (A,B), you could remove the index on A. The reason for this is that MySQL can use a leftmost prefix of a multiple-column index as if you had an index on just the leftmost columns. In other words, an index on (A, B) can be use when you search for combinations of A and B, or when you search for just A. You cannot remove the index that you have on just B, because B is not a leftmost index of (A, B). http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html Thanks, John on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Control Center autoincrement
Hello, May I specify a column to auto increment in the Control Center UI, either on or after creation? thanks Paul.
Re: mysqladmin shutdown command hangs
On Thu, 8 Jul 2004 [EMAIL PROTECTED] wrote: When there is an accidental communication breakdown between disk array and server on which mysql process is running (e.g, pulling out FC cable), this is being detected by our scripts and then we are trying to shutdown the mysql process by using mysqladmin shutdown option. However this command hangs and does not complete. I would imagine it would continue to hang until the array becomes available. It's probably in disk wait state, since mysql wants to do a clean shutdown, which I'm sure requires it touching a number of files in your db directory. Shutting it down seems like a bad thing to do; I would imagine stopping client access to the db would be a more useful action to take if you lose the array. Charles We tried the --force option also with the mysqladmin shutdown command, but it still hung. Does anybody have any suggestion/solution. Thanks, Ravi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqladmin shutdown command hangs
Charles, Sometimes, the connection to disk array will not be available to this server for long time and in such a case, we want another server to connect to the disk array and run mysql processes. Unfortunately, from our design, this cannot happen until the existing server shuts down. I am happy even if mysqladmin reports that mysqladmin shutdown has failed instead of hanging so that I can kill/stop the process using cruder methods. Thanks again, Ravi -Original Message- From: Charles Sprickman [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 10:07 PM To: Ravi T Ramachandra (WT01 - EMBEDDED PRODUCT ENGINEERING SOLUTIONS) Cc: [EMAIL PROTECTED] Subject: Re: mysqladmin shutdown command hangs On Thu, 8 Jul 2004 [EMAIL PROTECTED] wrote: When there is an accidental communication breakdown between disk array and server on which mysql process is running (e.g, pulling out FC cable), this is being detected by our scripts and then we are trying to shutdown the mysql process by using mysqladmin shutdown option. However this command hangs and does not complete. I would imagine it would continue to hang until the array becomes available. It's probably in disk wait state, since mysql wants to do a clean shutdown, which I'm sure requires it touching a number of files in your db directory. Shutting it down seems like a bad thing to do; I would imagine stopping client access to the db would be a more useful action to take if you lose the array. Charles We tried the --force option also with the mysqladmin shutdown command, but it still hung. Does anybody have any suggestion/solution. Thanks, Ravi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Control Center autoincrement
Hello, May I specify a column to auto increment from within the Control Center UI, either on or after creation? thanks Paul.
deleting a data of fields on a table
Is it possible to delete only a specific field on a table? lets say i have. table: dump field: - countid - random - name countid, is an auto increment field random, is a random words, etc name, is a field for specifiying where they belong. Now, how can i delete a specific data on each field? -- Louie Miranda http://www.axishift.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DISTINCT?
From the manual http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html: In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order. So, yes, the multi-column index on A,B will serve as the necessary index to make A a foreign key. You can still drop the single column index on A. Michael Emmett Bishop wrote: Paul, What about the case where column A is a foreign key? In that case would you be forced to keep the index on column A or could you use the A,B index since A is the left most prefix? Tripp --- Paul DuBois [EMAIL PROTECTED] wrote: At 19:26 -0700 7/7/04, John Mistler wrote: I am not certain from the documentation whether it is advisable to create a unique multi-column index on two columns that are already individually indexed. The individual indexes I assume I need for when I do a SELECT on those particular columns. The multi-column one I need for the reasons discussed below. Any one know? If you have indexes on column A and column B, then if you create a multiple-column unique index on (A,B), you could remove the index on A. The reason for this is that MySQL can use a leftmost prefix of a multiple-column index as if you had an index on just the leftmost columns. In other words, an index on (A, B) can be use when you search for combinations of A and B, or when you search for just A. You cannot remove the index that you have on just B, because B is not a leftmost index of (A, B). http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html Thanks, John on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
