Re: how to join two tables and include all records from one
LEFT JOIN is the way to go, but you need to make the invoice ID part of the JOIN condition rather than putting it in the WHERE clause. SELECT i.IDItems, i.Name, IFNULL(inv.Qty, 0) AS Qty FROM Items i LEFT JOIN Invoice inv ON i.IDItems = inv.IDItems AND inv.IDInvoice = 1001; Michael Cedric wrote: Hi, I'm quite new to mysql and I need to join two tables: Items IDItems Name 1 Orange 2 Apple 3 Bread 4 Milk ... Invoice IDInvoice IDItems Qty 1001 1 10 1001 3 2 1002 2 5 ... I need to get all items and for items included in invoice (IDInvoice) the qty Results for IDInvoice = 1001 IDItem NameQty 1 Orange 10 2 Apple 0 3 Bread 2 4 Milk0 Did somebody have an idea? I tried with LEFT JOIN / RIGHT JOIN / INNER JOIN... with no success Thanks for any help Cedric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query
shaun thornburgh wrote: Hi, I am having trouble with a LEFT JOIN. I have three tables: Users, Projects and Allocations. Users can be allocated to zero or more projects and a project can be allocated to zero or more users. Table descriptions are below. How can i select a users details and all of the pojects they are allocated to? This query is fine if the user is allocated to one or more projects: SELECT U.*, P.* FROM Users U, Allocations A, Projects P WHERE A.Project_ID = P.Project_ID AND A.User_ID = U.User_ID AND U.User_ID = '2'; Right. A user assigned to no projects won't have an entry in the Allocations table, so the JOIN condition isn't met. I'm assuming the problem is that the user is missing from the Allocations table, so you need a LEFT JOIN of Users to Allocations. And if i put a join in the query i get hundreds of results: Your original query above joins 3 tables. In your next query, you're changing one of the joins from an implicit inner join to a left join, not adding a join. SELECT U.*, P.* FROM Users U, Allocations A LEFT JOIN Projects P ON P.Project_ID = A.Project_ID AND A.User_ID = U.User_ID WHERE U.User_ID = '2'; First, though it's not the source of the problem, User_ID is an int, so lose the quotes around 2. You're forcing mysql to convert the string '2' to the integer 2. You have no join condition on the join of Users and Allocations, so you're getting the Cartesian product, hence the hundreds of results. (Yes, I see the A.User_ID = U.User_ID, but it's part of the ON clause for the LEFT JOIN of A to P, so it's not doing what you meant.) SELECT U.*, P.* FROM Users U LEFT JOIN Allocations A ON U.User_ID = A.User_ID LEFT JOIN Projects P ON A.Project_ID = P.Project_ID WHERE U.User_ID = 2; Michael Any help here would be great, many thanks. mysql DESCRIBE Users; +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | User_ID | int(11) | | PRI | NULL| auto_increment | | User_Username| varchar(40) | | | || | User_Password| varchar(20) | YES | | NULL || | User_Name| varchar(100) | | | || | User_Type| varchar(20) | | | Nurse || | User_Email | varchar(100) | YES | | NULL || +--+---+--+-+-++ 17 rows in set (0.01 sec) mysql DESCRIBE Allocations; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | Project_ID | int(11) | | PRI | 0 | | | User_ID| int(11) | | PRI | 0 | | ++-+--+-+-+---+ 2 rows in set (0.00 sec) mysql DESCRIBE Projects; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | || +--+--+--+-+-++ 5 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating Relational Links
Hi everyone. It occurred to me that I may need a table in my database related to the primary table to hold such things as login time and date etc. Creating the table is straight forward enough but how do I create the relational link or do I simply ensure that the foreign key is assigned and then call the table up whenever I need it? Michael Mason Business Support Services Arras People Tel: 01706 342310 Mobile: 07793 782287 Fax: 01706 642754 Member of the Recruitment Employment Confederation (00052055) The views expressed in this mail are entirely those of the sender, and do not necessarily represent the views or position of Arras Services Ltd. The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in relation to the contents of this information is strictly prohibited and may be unlawful. Neither the sender nor the represented institution is liable for the correct and complete transmission of the contents of this e-mail, or for its timely receipt.
how to select last date by current time?
List, How can i issue a query that can select last dateposted (FIELD)? My table: - dateposted, format: -MM-DD This is my current sql query which catches in desc order for dateposted and list only 10 sql data. select dateposted, filename, description from datafiles where office = 'AC/PA' order by dateposted desc limit 10; -- 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]
[CONNECTION PROBLEMS]
To have a better performence I have installed a new server running solaris8 and mysql version 3.23.53. The old server is running solaris 7 and mysql 3.23.48-max. I also transferred the database to the new server and tried to connect the database with mysql front. Everything worked fine. Then I tried to connect with a application written in VB. It now seems that I can send a select string and look at te results, but cannot add or change anything in the tables. The first error appears when a begin transaction is started. -- Peter Bruggink Manager mechanical Design +31 76 5792732 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] *Steelweld BV* Terheijdenseweg 169 The Netherlands www.steelweld.com http://www.steelweld.com/ *DISCLAIMER* The information transmitted is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed. If you received this in error, please contact the sender and delete the material from any computer. This mail has been checked for all known viruses by McAfee Virusscan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27
Are you files being created in /tmp? How big is this partition? I wondered about that, but I've set the mysql tmpdir to /proxydb/mysql/tmp, and the error does actually say: Error writing file '/proxydb/mysql/tmp/STTbtrac' Also I couldn't see any mysql files in /tmp. It's tricky isn't it?! JS. -Original Message- From: J S To: [EMAIL PROTECTED] Sent: 7/15/04 2:29 PM Subject: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Hi, I'm trying to create an index using the command: CREATE INDEX urlindex ON internet_usage (urlid) | Field| Type | Null | Key | Default | Extra | | urlid| int(10) unsigned | | | 0 | | But I keep getting the following error: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27) perror says: $ perror 3 Error code 3: The process does not exist. $ perror 27 Error code 27: A file cannot be larger than the value set by ulimit. $ I'm using mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc on aix 4.3. The status of this table is: | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | | internet_usage | MyISAM | Fixed | 324936160 | 32 | 10397957120 |137438953471 | 2888631296 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-13 20:27:06 | 2004-07-09 14:13:20 | max_rows=4294967295 | | I can't see any reason why there would be an error 27. The filesystem is largefile enabled and has plenty of space: $ df -k . Filesystem1024-blocks Free %UsedIused %Iused Mounted on /dev/sawmill0265601536 30921916 53% 1064 1% /proxydb Also the filesize ulimit is unlimited: # su - mysql $ cd /proxydb/mysql/tmp $ cat load-SG_BSGL01_main_46042921 test $ ls -l test -rw-r--r-- 1 mysqlmysql4239906888 Jul 15 20:20 test # su - mysql $ ulimit -a time(seconds)unlimited file(blocks) unlimited data(kbytes) unlimited stack(kbytes)unlimited memory(kbytes) 50 coredump(blocks) unlimited nofiles(descriptors) 2000 I'm not really sure why I get the error 3 though? Can anyone help me out please? Thanks, JS. _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to select last date by current time?
This is my current sql query which catches in desc order for dateposted and list only 10 sql data. select dateposted, filename, description from datafiles where office = 'AC/PA' order by dateposted desc limit 10; This will probably work fine. Anyway, make sure that dateposted has DATE field format. Otherwise sorting will probably take much longer and can sometimes have strange effects ;) Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join synatx
hello, reading the documentation I'm confused to as which is better: SELECT * FROM T1,T2 where T1.K1 = T2.K2 AND T1.K2=T2.K2 AND ...otherconditions... or should I do SELECT * FROM T1 NATURAL JOIN T2 WHERE ..other conditions.. or should I SELECT * FROM T1 JOIN T2 USING (K1,K2) WHERE ..other conditions... thanks, Singer -- Singer X.J. Wang, Ph.D. Candidate Faculty of Computer Science, Dalhousie University 6050 University Avenue, Halifax, NS, Canada, B3H 1W5 Email:[EMAIL PROTECTED] Fax: (902) 492-1517 WWW: http://www.singerwang.com Schedule: http://schedule.singerwang.com -- Your reading of this email is contributing to the eventual heat death of the universe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STT btrac' (Errcode: 27
Can you explicitly create temporary tables? -Original Message- From: J S To: [EMAIL PROTECTED] Sent: 7/16/04 3:54 AM Subject: RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Are you files being created in /tmp? How big is this partition? I wondered about that, but I've set the mysql tmpdir to /proxydb/mysql/tmp, and the error does actually say: Error writing file '/proxydb/mysql/tmp/STTbtrac' Also I couldn't see any mysql files in /tmp. It's tricky isn't it?! JS. -Original Message- From: J S To: [EMAIL PROTECTED] Sent: 7/15/04 2:29 PM Subject: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Hi, I'm trying to create an index using the command: CREATE INDEX urlindex ON internet_usage (urlid) | Field| Type | Null | Key | Default | Extra | | urlid| int(10) unsigned | | | 0 | | But I keep getting the following error: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27) perror says: $ perror 3 Error code 3: The process does not exist. $ perror 27 Error code 27: A file cannot be larger than the value set by ulimit. $ I'm using mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc on aix 4.3. The status of this table is: | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | | internet_usage | MyISAM | Fixed | 324936160 | 32 | 10397957120 |137438953471 | 2888631296 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-13 20:27:06 | 2004-07-09 14:13:20 | max_rows=4294967295 | | I can't see any reason why there would be an error 27. The filesystem is largefile enabled and has plenty of space: $ df -k . Filesystem1024-blocks Free %UsedIused %Iused Mounted on /dev/sawmill0265601536 30921916 53% 1064 1% /proxydb Also the filesize ulimit is unlimited: # su - mysql $ cd /proxydb/mysql/tmp $ cat load-SG_BSGL01_main_46042921 test $ ls -l test -rw-r--r-- 1 mysqlmysql4239906888 Jul 15 20:20 test # su - mysql $ ulimit -a time(seconds)unlimited file(blocks) unlimited data(kbytes) unlimited stack(kbytes)unlimited memory(kbytes) 50 coredump(blocks) unlimited nofiles(descriptors) 2000 I'm not really sure why I get the error 3 though? Can anyone help me out please? Thanks, JS. _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- 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: [CONNECTION PROBLEMS]
What error messages are you receiving? -Original Message- From: Peter Bruggink To: [EMAIL PROTECTED] Sent: 7/16/04 3:19 AM Subject: [CONNECTION PROBLEMS] To have a better performence I have installed a new server running solaris8 and mysql version 3.23.53. The old server is running solaris 7 and mysql 3.23.48-max. I also transferred the database to the new server and tried to connect the database with mysql front. Everything worked fine. Then I tried to connect with a application written in VB. It now seems that I can send a select string and look at te results, but cannot add or change anything in the tables. The first error appears when a begin transaction is started. -- Peter Bruggink Manager mechanical Design +31 76 5792732 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] *Steelweld BV* Terheijdenseweg 169 The Netherlands www.steelweld.com http://www.steelweld.com/ *DISCLAIMER* The information transmitted is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed. If you received this in error, please contact the sender and delete the material from any computer. This mail has been checked for all known viruses by McAfee Virusscan. -- 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: Creating Relational Links
Have you looked at InnoDB tables? -Original Message- From: Michael Mason To: 'MySQL Mailing List' Sent: 7/16/04 2:06 AM Subject: Creating Relational Links Hi everyone. It occurred to me that I may need a table in my database related to the primary table to hold such things as login time and date etc. Creating the table is straight forward enough but how do I create the relational link or do I simply ensure that the foreign key is assigned and then call the table up whenever I need it...? Michael Mason Business Support Services Arras® People Tel: 01706 342310 Mobile: 07793 782287 Fax: 01706 642754 http://www.arraspeople.co.uk/ The Project Management Recruitment Specialists Member of the Recruitment Employment Confederation (00052055) The views expressed in this mail are entirely those of the sender, and do not necessarily represent the views or position of Arras Services Ltd. The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in relation to the contents of this information is strictly prohibited and may be unlawful. Neither the sender nor the represented institution is liable for the correct and complete transmission of the contents of this e-mail, or for its timely receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: identifying multi-byte characters / coverting
hi that may be but it's got weird beahviour. for example, when u get that in an edit box (like the one u compose mail in) it looks like: (the upper comma). but when i read ur mail, it appeared as it's html character code. in fact, while reading this u may see it as the character code and not the character itself and vice-versa when composing a reply. my question is: does mysql treat/store it as the character code (which would make sense) only? and does it have the upper comma look only when viewed in a browser edit box? also appears like that when u don't apply htmlentities to the output. ty abs --- [EMAIL PROTECTED] wrote: Hi abs, My first post seem to be vanished, so excuse me if this is double. I am not sure if I understand youir problem correctly. Isn' what you describe as the html code for the upper komma or how you want to call it ? ___ALL-NEW Yahoo! Messenger - so many all-new ways to express yourself http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27
Can you explicitly create temporary tables? Yes, I have full priviledges. Do you mean I could copy the internet_usage table to a temp table with new keys? JS. -Original Message- From: J S To: [EMAIL PROTECTED] Sent: 7/16/04 3:54 AM Subject: RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Are you files being created in /tmp? How big is this partition? I wondered about that, but I've set the mysql tmpdir to /proxydb/mysql/tmp, and the error does actually say: Error writing file '/proxydb/mysql/tmp/STTbtrac' Also I couldn't see any mysql files in /tmp. It's tricky isn't it?! JS. -Original Message- From: J S To: [EMAIL PROTECTED] Sent: 7/15/04 2:29 PM Subject: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Hi, I'm trying to create an index using the command: CREATE INDEX urlindex ON internet_usage (urlid) | Field| Type | Null | Key | Default | Extra | | urlid| int(10) unsigned | | | 0 | | But I keep getting the following error: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27) perror says: $ perror 3 Error code 3: The process does not exist. $ perror 27 Error code 27: A file cannot be larger than the value set by ulimit. $ I'm using mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc on aix 4.3. The status of this table is: | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | | internet_usage | MyISAM | Fixed | 324936160 | 32 | 10397957120 |137438953471 | 2888631296 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-13 20:27:06 | 2004-07-09 14:13:20 | max_rows=4294967295 | | I can't see any reason why there would be an error 27. The filesystem is largefile enabled and has plenty of space: $ df -k . Filesystem1024-blocks Free %UsedIused %Iused Mounted on /dev/sawmill0265601536 30921916 53% 1064 1% /proxydb Also the filesize ulimit is unlimited: # su - mysql $ cd /proxydb/mysql/tmp $ cat load-SG_BSGL01_main_46042921 test $ ls -l test -rw-r--r-- 1 mysqlmysql4239906888 Jul 15 20:20 test # su - mysql $ ulimit -a time(seconds)unlimited file(blocks) unlimited data(kbytes) unlimited stack(kbytes)unlimited memory(kbytes) 50 coredump(blocks) unlimited nofiles(descriptors) 2000 I'm not really sure why I get the error 3 though? Can anyone help me out please? Thanks, JS. _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STT btrac' (Errcode: 27
Yes. Can you CREATE TEMP TABLE ? -Original Message- From: J S To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 7/16/04 7:34 AM Subject: RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Can you explicitly create temporary tables? Yes, I have full priviledges. Do you mean I could copy the internet_usage table to a temp table with new keys? JS. -Original Message- From: J S To: [EMAIL PROTECTED] Sent: 7/16/04 3:54 AM Subject: RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Are you files being created in /tmp? How big is this partition? I wondered about that, but I've set the mysql tmpdir to /proxydb/mysql/tmp, and the error does actually say: Error writing file '/proxydb/mysql/tmp/STTbtrac' Also I couldn't see any mysql files in /tmp. It's tricky isn't it?! JS. -Original Message- From: J S To: [EMAIL PROTECTED] Sent: 7/15/04 2:29 PM Subject: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Hi, I'm trying to create an index using the command: CREATE INDEX urlindex ON internet_usage (urlid) | Field| Type | Null | Key | Default | Extra | | urlid| int(10) unsigned | | | 0 | | But I keep getting the following error: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27) perror says: $ perror 3 Error code 3: The process does not exist. $ perror 27 Error code 27: A file cannot be larger than the value set by ulimit. $ I'm using mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc on aix 4.3. The status of this table is: | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | | internet_usage | MyISAM | Fixed | 324936160 | 32 | 10397957120 |137438953471 | 2888631296 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-13 20:27:06 | 2004-07-09 14:13:20 | max_rows=4294967295 | | I can't see any reason why there would be an error 27. The filesystem is largefile enabled and has plenty of space: $ df -k . Filesystem1024-blocks Free %UsedIused %Iused Mounted on /dev/sawmill0265601536 30921916 53% 1064 1% /proxydb Also the filesize ulimit is unlimited: # su - mysql $ cd /proxydb/mysql/tmp $ cat load-SG_BSGL01_main_46042921 test $ ls -l test -rw-r--r-- 1 mysqlmysql4239906888 Jul 15 20:20 test # su - mysql $ ulimit -a time(seconds)unlimited file(blocks) unlimited data(kbytes) unlimited stack(kbytes)unlimited memory(kbytes) 50 coredump(blocks) unlimited nofiles(descriptors) 2000 I'm not really sure why I get the error 3 though? Can anyone help me out please? Thanks, JS. _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECTing from different databases/users
Hi. Is it possible to make a select from different tables that reside in different databases that have different usernames? The fact is that I'm using Helm Web Hosting Control tool for creating databases and users, and it let's me create only one user per database, so I can't assign a user to multiple databases (and my select problem would go) Thanks in advance!
Re: Need MORE help with query after all...
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: Have you considered splitting this into two queries? One query can gather information from your token and token_ins tables. The other would join the first queries results to the other tables to complete your original query. Depending on your data, one of these subqueries should return a smaller set (fewer rows) and you should execute the smaller one FIRST. Because of that you may want to flip the order of execution of these queries around (modifying as appropriate for the new order) and check the cd, c, and d tables first and join those results to the t, ti, t2 and ti2 tables. Yes, this might help. BTW, you don't need a temp table for that. MySQL allows controlling the join order by STRAIGHT_JOIN. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to select last date by current time?
Louie, Do you want just the most recent date? You could run : SELECT MAX(dateposted) FROM datafiles WHERE office='AC/PA' OR if you want just the most recent record: SELECT dateposted, filename FROM datafiles WHERE office='AC/PA' ORDER BY dateposted desc LIMIT 1; OR you could also ask for that same record this way (depending on your MySQL version): SELECT dateposted, filename FROM datafiles d INNER JOIN (SELECT MAX(dateposted) as maxdate FROM datafiles WHERE office='AC/PA') as mx ON mx.maxdate = d.dateposted WHERE office='AC/PA' Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Louie Miranda [EMAIL PROTECTED] wrote on 07/16/2004 03:11:02 AM: List, How can i issue a query that can select last dateposted (FIELD)? My table: - dateposted, format: -MM-DD This is my current sql query which catches in desc order for dateposted and list only 10 sql data. select dateposted, filename, description from datafiles where office = 'AC/PA' order by dateposted desc limit 10; -- 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: SELECTing from different databases/users
Does you have the rights to use the GRANT and REVOKE commands directly? If not those, do you have rights to INSERT and DELETE from the users table of the mysql database? If you do, you could possibly bypass their one user per databse rule and create your own. Otherwise I think you are stuck as each query's privileges can only be evaluated in the context of a single user. (No query can assume two identities at the same time.) Just a suggestion, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 07/16/2004 09:01:21 AM: Hi. Is it possible to make a select from different tables that reside in different databases that have different usernames? The fact is that I'm using Helm Web Hosting Control tool for creating databases and users, and it let's me create only one user per database, so I can't assign a user to multiple databases (and my select problem would go) Thanks in advance!
RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27
Yes. Can you CREATE TEMP TABLE ? I managed this: mysql CREATE TEMPORARY TABLE test - select * from internet_usage; Query OK, 324936160 rows affected (54 min 31.64 sec) Records: 0 Duplicates: 324936160 Warnings: 0 Can you create a temp table with keys then? -Original Message- From: J S To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 7/16/04 7:34 AM Subject: RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Can you explicitly create temporary tables? Yes, I have full priviledges. Do you mean I could copy the internet_usage table to a temp table with new keys? JS. -Original Message- From: J S To: [EMAIL PROTECTED] Sent: 7/16/04 3:54 AM Subject: RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Are you files being created in /tmp? How big is this partition? I wondered about that, but I've set the mysql tmpdir to /proxydb/mysql/tmp, and the error does actually say: Error writing file '/proxydb/mysql/tmp/STTbtrac' Also I couldn't see any mysql files in /tmp. It's tricky isn't it?! JS. -Original Message- From: J S To: [EMAIL PROTECTED] Sent: 7/15/04 2:29 PM Subject: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Hi, I'm trying to create an index using the command: CREATE INDEX urlindex ON internet_usage (urlid) | Field| Type | Null | Key | Default | Extra | | urlid| int(10) unsigned | | | 0 | | But I keep getting the following error: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27) perror says: $ perror 3 Error code 3: The process does not exist. $ perror 27 Error code 27: A file cannot be larger than the value set by ulimit. $ I'm using mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc on aix 4.3. The status of this table is: | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | | internet_usage | MyISAM | Fixed | 324936160 | 32 | 10397957120 |137438953471 | 2888631296 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-13 20:27:06 | 2004-07-09 14:13:20 | max_rows=4294967295 | | I can't see any reason why there would be an error 27. The filesystem is largefile enabled and has plenty of space: $ df -k . Filesystem1024-blocks Free %UsedIused %Iused Mounted on /dev/sawmill0265601536 30921916 53% 1064 1% /proxydb Also the filesize ulimit is unlimited: # su - mysql $ cd /proxydb/mysql/tmp $ cat load-SG_BSGL01_main_46042921 test $ ls -l test -rw-r--r-- 1 mysqlmysql4239906888 Jul 15 20:20 test # su - mysql $ ulimit -a time(seconds)unlimited file(blocks) unlimited data(kbytes) unlimited stack(kbytes)unlimited memory(kbytes) 50 coredump(blocks) unlimited nofiles(descriptors) 2000 I'm not really sure why I get the error 3 though? Can anyone help me out please? Thanks, JS. _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STT btrac' (Errcode: 27
Yes you can. -Original Message- From: J S To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 7/16/04 8:35 AM Subject: RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Yes. Can you CREATE TEMP TABLE ? I managed this: mysql CREATE TEMPORARY TABLE test - select * from internet_usage; Query OK, 324936160 rows affected (54 min 31.64 sec) Records: 0 Duplicates: 324936160 Warnings: 0 Can you create a temp table with keys then? -Original Message- From: J S To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 7/16/04 7:34 AM Subject: RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Can you explicitly create temporary tables? Yes, I have full priviledges. Do you mean I could copy the internet_usage table to a temp table with new keys? JS. -Original Message- From: J S To: [EMAIL PROTECTED] Sent: 7/16/04 3:54 AM Subject: RE: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Are you files being created in /tmp? How big is this partition? I wondered about that, but I've set the mysql tmpdir to /proxydb/mysql/tmp, and the error does actually say: Error writing file '/proxydb/mysql/tmp/STTbtrac' Also I couldn't see any mysql files in /tmp. It's tricky isn't it?! JS. -Original Message- From: J S To: [EMAIL PROTECTED] Sent: 7/15/04 2:29 PM Subject: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27 Hi, I'm trying to create an index using the command: CREATE INDEX urlindex ON internet_usage (urlid) | Field| Type | Null | Key | Default | Extra | | urlid| int(10) unsigned | | | 0 | | But I keep getting the following error: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac' (Errcode: 27) perror says: $ perror 3 Error code 3: The process does not exist. $ perror 27 Error code 27: A file cannot be larger than the value set by ulimit. $ I'm using mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc on aix 4.3. The status of this table is: | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | | internet_usage | MyISAM | Fixed | 324936160 | 32 | 10397957120 |137438953471 | 2888631296 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-13 20:27:06 | 2004-07-09 14:13:20 | max_rows=4294967295 | | I can't see any reason why there would be an error 27. The filesystem is largefile enabled and has plenty of space: $ df -k . Filesystem1024-blocks Free %UsedIused %Iused Mounted on /dev/sawmill0265601536 30921916 53% 1064 1% /proxydb Also the filesize ulimit is unlimited: # su - mysql $ cd /proxydb/mysql/tmp $ cat load-SG_BSGL01_main_46042921 test $ ls -l test -rw-r--r-- 1 mysqlmysql4239906888 Jul 15 20:20 test # su - mysql $ ulimit -a time(seconds)unlimited file(blocks) unlimited data(kbytes) unlimited stack(kbytes)unlimited memory(kbytes) 50 coredump(blocks) unlimited nofiles(descriptors) 2000 I'm not really sure why I get the error 3 though? Can anyone help me out please? Thanks, JS. _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives:
Re: Bug in fulltext index creation
Hi again. PROCESS LIST is: Creating tmp file Repair by sorting The problem occurs while repair by sorting. myisam parameters have been increased accordingly: myisam max extra sort file size = 15000M myisam max sort file size = 15000M There must be a bug somewhere? What do you suggest? Vincent Hi! On Jul 06, Vincent Bouret wrote: Hi, I got the following values: key_buffer_size = 256M myisam max extra sort file size = 8000M myisam max sort file size = 8000M myisam sort buffer size = 128M But that big table (MYD = 2397 MB), rows = 5 355 866 still won't index in full text. Disk space is enough (25GB of free space), no error reported in the log. Using MySQL 4.0.14, doesn't work better with 4.0.20 (running under Win32). Where should I look at? probably myisam_max_sort_file_size is too small for 2GB table. read the manual about this variable, and increase accordingly. check show processlist during indexing Regards, Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
confused about character sets in mysql 4.1.3b
Hello list, I have already installed mysql 4.1.3 beta to my windows xp. I set default-caharacter-set to latin5 and default-collation to latin5_turkish_ci in my.ini configuration file. Because I want latin5 my default character set. Then I restarted mysql service and looked up character set and collation variables. And get cofused. - In WinMySQLAdmin1.4 variables tab I see the following results: character_set_client = latin1 character_set_connection = latin1 character_set_database = latin5 character_set_results = latin1 character_set_server = latin5 character_set_system = utf8 collation_connection = latin1_swedish_ci collation_database = latin5_turkish_ci collation_server = latin5_turkish_ci - When i execute show variables like '%character%' and show variables like '%collation%' queries from the command line i get the same results. - When i execute the same queries from phpMyAdmin 2.5.7-pl1 i get different result set. character set client = latin5 character set connection = latin5 character set database = latin5 character set results = latin5 character set server = latin5 character set system = utf8 collation connection = latin5_turkish_ci collation database = latin5_turkish_ci collation server = latin5_turkish_ci First off all I could not understand why the results are different. And second, does not all my variables have to be latin5 and latin5_turkish_ci because of the configuration I have done in my.ini file? Thanks in advace. Harun __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql growing pains, 4 days to create index on one table!
Donny Simonton wrote: Matt, I've been reading this thread for a while and at this point, I would say that you would need to provide the table structures and queries that you are running. For example, we have one table that has 8 billion rows in it and it close to 100 gigs and we can hammer it all day long without any problems. It really depends on how you are doing things. But as far as you mentioning about mysql not using multiple indexes, it does. You just have to create an index on multiple fields at one time. I've got tables with 10 fields in one index, now the trick with mysql is that you must use all top 10 fields in your where clause for mysql to really take advantage of the index. But I would definitely send the list your table structure with your indexes and some of your selects and inserts. You can always change the names of things if you don't want people to know the names of everything. Just my 2 cents. Original email has the table structure, query speed is not an issue (it is, but I will deal with that later) the issue is insert speed, I get 150k-1M records daily, of these, only 5-10 % are new records, I load the EBCDIC file into a temp table, and then do insert ignore into historytable select * from temp table and I cant create multiple field indexes, I would need 100 indexes on a table, the index side already exceeds the table size, I tried to add an index on date, but gave up because it ran for 2 days and was not done yet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deployment of mySQL for high reliability/availability
Does anyone have any experience setting up a group of servers for high reliability/availability? We are planning to deploy a WebObjects application but need to design a configuration that will give us a VERY high level of availability. Our current thoughts are to have three machines: 1. Xserve G5 with mirrored Hard drives running - MacOS 10.3.x - mySQL (Replication - master) - WebObjects (HTTP Adapter) - WebObjects Application instance(s) 2. Xserve G5 with mirrored Hard drives running - MacOS 10.3.x - mySQL (Replication - slave) 3. Xserve G5 cluster node running: - MacOS 10.3.x - WebObjects Application instance(s) If machine 1 fails then machine 2 will be configured with IP Failover to become the mySQL master and WebObjects would be launched with the adapter and our WebObjects application instances. One issue we have found is that when machine 1 reboots, it would failover to machine 2 momentarily. MySQL would be configured to automatically send its data back to machine 1 when it comes back up. For a simple reboot this seems unreasonable, it takes about 20 minutes to send the data back (tables would have to be locked). If machine 2 fails then nothing happens. If machine 3 fails then the WO Adapter would no longer delegate requests to it. As our needs increase we would add additional cluster nodes running WO Application instances. Eventually we will migrate to clustering, mySQL v4.1. Any thoughts, comments? Thanks, kib -- Klaus Berkling Systems Administrator/Engineer DynEd International, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL 3.23.58 directory structure
Hello, I am upgrading from MYSQL 3.22.30 to 3.23.58 on a Tru64 Alpha 4.0F. The set of commands below are used to create a Make file. My problem is that I want include files to be placed under /usr/local/mysql/include; However, with the configuration below, they are placed under /usr/local/mysql/include/mysql. How could I change the configuration parameters so all include, lib and data are placed under /usr/local/mysql/include, /usr/local/mysql/lib and /usr/local/mysql/data respectively? CC=cc -pthread\ CXX=cxx -pthread -O\ export CC CFLAGS CXX CXXFLAGS\ ./configure \ --prefix=/usr/local/mysql-3.23.58 \ --with-low-memory \ --enable-large-files \ --enable-shared=yes \ --with-named-thread-libs=-lpthread -lmach -lexc -lc\ --localstatedir=/usr/local/mysql-3.23.58/data \ --libdir=/usr/local/mysql-3.23.58/lib \ --with-docs\ --includedir=/usr/local/mysql-3.23.58/include Thank you for any help, -Teresa This is a PRIVATE message. If you are not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. NOTE: Regardless of content, this e-mail shall not operate to bind CSC to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mutiple MSQL installation on G5- will performance increase ?
Hi all, System details: PowerMac G5 1.6 GHz dual processor 3 G RAM Mac OS X (10.3.4) Application : MYSQL (mysql Ver 12.20 Distrib 4.0.13, for apple-darwin6.6 (powerpc)) + APPACHE I have couple of database on a single installation of MySQL. One of these databases is static (only selects) and is really huge (it has tables with 500,000,000 rows) and the remaining databases are comparatively small and have frequent updates. All tables are MyISAM type. I am currently considering ways to improve the perfromance (select) of the huge database. There wont be more than 5 - 10 users accessing this database concurrently. Current System variables key buffer size 8388600 sort buffer size 2097144 join buffer size 131072 max heap table size 16777216 max join size 4294967295 max sort length 1024 myisam max sort file size 2147483647 myisam sort buffer size 8388608 Upto my understanding mysql runs on a single thread and is not capable of utilizing both the processors. (please correct me if i am wrong). I would like to know if I could see any performance improvement if I do a second installation of MySQL and move the huge database to the new installation. In that case please suggest me if it is better to install by compiling the source code or is there any package available that can be used directly. Also it would be helpful if you could give me some hints on what system variables to set and what table types to use. After doing the basic setup, I will play with the queries to optimize them. Thanks for your help Kareen T __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! 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]
Performance
Mysql 4.0.14 2 Tables: Table1: Indexed on Field1 (primary key), Field2 Table2: Indexed on Field1 (primary key), Field2 (Foreign key from table1) When I do this explain select a.*, b.* from table1 a, table2 b, where a.field1 = b.field2 and a.field2 = 'value'. returns the result: Table typepossible_keys Key Key_len ref a ALL Primary, Field2 b ref Field2 Field2 37 a.Field1 And it takes along time to process (~25 seconds) on only ~5000 records. Is there anything we do wrong here? regards ___ALL-NEW Yahoo! Messenger - so many all-new ways to express yourself http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mutiple MSQL installation on G5- will performance increase ?
Kart v wrote: sort buffer size 2097144 join buffer size 131072 max heap table size 16777216 max join size 4294967295 max sort length 1024 myisam max sort file size 2147483647 myisam sort buffer size 8388608 Upto my understanding mysql runs on a single thread and is not capable of utilizing both the processors. (please correct me if i am wrong). MySQL uses both processors on a G5 if more than one query is running. If there is only a single query running it is limited to one processor. So I don't think running multiple instances makes sense. Also it would be helpful if you could give me some hints on what system variables to set and what table types to use. After doing the basic setup, I will play with the queries to optimize them. Your key buffer looks very small, assuming you are using MyISAM tables: key buffer size 8388600 I'd look at http://dev.mysql.com/doc/mysql/en/Server_system_variables.html as a first cut. You'll get a lot of benefit out of these and optimizing queries. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance
What does the cardinality look like for table1? -Original Message- From: A Z To: [EMAIL PROTECTED] Sent: 7/16/04 10:53 AM Subject: Performance Mysql 4.0.14 2 Tables: Table1: Indexed on Field1 (primary key), Field2 Table2: Indexed on Field1 (primary key), Field2 (Foreign key from table1) When I do this explain select a.*, b.* from table1 a, table2 b, where a.field1 = b.field2 and a.field2 = 'value'. returns the result: Table typepossible_keys Key Key_len ref a ALL Primary, Field2 b ref Field2 Field2 37 a.Field1 And it takes along time to process (~25 seconds) on only ~5000 records. Is there anything we do wrong here? regards ___ALL-NEW Yahoo! Messenger - so many all-new ways to express yourself http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mutiple MSQL installation on G5- will performance increase ?
--- Ware Adams [EMAIL PROTECTED] wrote: sort buffer size 2097144 join buffer size 131072 max heap table size 16777216 max join size 4294967295 max sort length 1024 myisam max sort file size 2147483647 myisam sort buffer size 8388608 Upto my understanding mysql runs on a single thread and is not capable of utilizing both the processors. (please correct me if i am wrong). MySQL uses both processors on a G5 if more than one query is running. If there is only a single query running it is limited to one processor. So I don't think running multiple instances makes sense. ok. Also it would be helpful if you could give me some hints on what system variables to set and what table types to use. After doing the basic setup, I will play with the queries to optimize them. Your key buffer looks very small, assuming you are using MyISAM tables: I was in a view that if the key index size is more than the available memory there is no point in increasing the key buffer size since anyway it will use the disk read. If I am wrong, could you suggest me a value for this? Thanks again Kareen __ 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]
Re: Mysql growing pains, 4 days to create index on one table!
Here's another option to load without requiring a primary key (requires a LOT of extra disk space and fast CPU, and a batch window to run). Load the new daily data into the table without checking for dupes. Then create a new version of the table with distinct values. Something like this (assuming your table has 2 columns which are char (3) and char (5) for simplicity's sake) in pseudocode: (remember to pad all columns to the maximum width to make them uniform, right spaces on char, left zeroes on numerics) CREATE NEW_TABLE AS SELECT SUBSTR (DISTINCT (RPAD(COL1,3) || RPAD(COL2,5)),1,3) AS COL1, SUBSTR (DISTINCT (RPAD(COL1,3) || RPAD(COL2,5)),4,5) AS COL2 FROM OLD_TABLE matt ryan [EMAIL PROTECTED] 07/16/2004 07:43 AM To: cc: [EMAIL PROTECTED] Subject:Re: Mysql growing pains, 4 days to create index on one table! Donny Simonton wrote: Matt, I've been reading this thread for a while and at this point, I would say that you would need to provide the table structures and queries that you are running. For example, we have one table that has 8 billion rows in it and it close to 100 gigs and we can hammer it all day long without any problems. It really depends on how you are doing things. But as far as you mentioning about mysql not using multiple indexes, it does. You just have to create an index on multiple fields at one time. I've got tables with 10 fields in one index, now the trick with mysql is that you must use all top 10 fields in your where clause for mysql to really take advantage of the index. But I would definitely send the list your table structure with your indexes and some of your selects and inserts. You can always change the names of things if you don't want people to know the names of everything. Just my 2 cents. Original email has the table structure, query speed is not an issue (it is, but I will deal with that later) the issue is insert speed, I get 150k-1M records daily, of these, only 5-10 % are new records, I load the EBCDIC file into a temp table, and then do insert ignore into historytable select * from temp table and I cant create multiple field indexes, I would need 100 indexes on a table, the index side already exceeds the table size, I tried to add an index on date, but gave up because it ran for 2 days and was not done yet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mutiple MSQL installation on G5- will performance increase ?
Kart v wrote: Also it would be helpful if you could give me some hints on what system variables to set and what table types to use. After doing the basic setup, I will play with the queries to optimize them. Your key buffer looks very small, assuming you are using MyISAM tables: key buffer size 8388600 I was in a view that if the key index size is more than the available memory there is no point in increasing the key buffer size since anyway it will use the disk read. If I am wrong, could you suggest me a value for this? That's true, and swapping will make performance horrible. But your original email said: PowerMac G5 1.6 GHz dual processor 3 G RAM Mac OS X (10.3.4) So you have 3 GB RAM and your using only about 8 MB for key_buffer_size. What else is running on the box? If it's only MySQL try cranking up key_buffer_size. If it's only MySQL on the machine, I might start at 1.5 GB. Watch top while your queries are running. You want mydqld at or close to 100% cpu (only one query running) or 200% (2 or more queries running). If your not using the cpu fully, you definitely need to optimize mysqld's settings and/or optimize your queries. Even if the cpu is running full out, optimizing queries to examine fewer rows can help. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mutiple MSQL installation on G5- will performance increase ?
You need to first determine what the bottleneck is. If it's disk I/O that's slowing you down, then adding another instance of MySQL may actually hurt you. If that table is physically large, it's probably your disk subsystem holding you back. I don't know what you disk setup is like. For about $250 you could get two fast IDE disks and an IDE card and stripe the drives (RAID 0?). Move just the big table to the striped drives and that should eliminate disk contention and give you fast access to the table. Since it's static, you don't have to worry too much about losing everything if a disk goes bad. Meaning, keep a copy on another disk. On Jul 16, 2004, at 11:17 AM, Kart v wrote: Hi all, System details: PowerMac G5 1.6 GHz dual processor 3 G RAM Mac OS X (10.3.4) Application : MYSQL (mysql Ver 12.20 Distrib 4.0.13, for apple-darwin6.6 (powerpc)) + APPACHE I have couple of database on a single installation of MySQL. One of these databases is static (only selects) and is really huge (it has tables with 500,000,000 rows) and the remaining databases are comparatively small and have frequent updates. All tables are MyISAM type. I am currently considering ways to improve the perfromance (select) of the huge database. There wont be more than 5 - 10 users accessing this database concurrently. Current System variables key buffer size 8388600 sort buffer size 2097144 join buffer size 131072 max heap table size 16777216 max join size 4294967295 max sort length 1024 myisam max sort file size 2147483647 myisam sort buffer size 8388608 Upto my understanding mysql runs on a single thread and is not capable of utilizing both the processors. (please correct me if i am wrong). I would like to know if I could see any performance improvement if I do a second installation of MySQL and move the huge database to the new installation. In that case please suggest me if it is better to install by compiling the source code or is there any package available that can be used directly. Also it would be helpful if you could give me some hints on what system variables to set and what table types to use. After doing the basic setup, I will play with the queries to optimize them. Thanks for your help Kareen T __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! 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] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql growing pains, 4 days to create index on one table!
Since you have a temp table created (no keys I assume), use the command mysqldump -v -e -n -t dbname tablename filename.sql This should create insert statements with many values in a single insert. Then use the client program to insert them to you db. mysql -u matt -p dbname filename.sql This is very fast way to insert rows. Speeds up insertion by at least 10x on my large tables (11 million rows). I noticed someone with 1000x more rows w/o problems. BTW, by forcing the table to have packed keys, the docs say it will slow your insertion. Maybe not that much, i don't know. David Isnt the text file it creates, going to insert the records back into the temp table when I load it back in? Does this do insert ignore or insert replace? I need to control that, on some tables I do insert ignore, on others i do insert replace. Almost all of the speed issue is read related, the disk writes are nearly 0, the reads are as fast as the drive can run, reading to see if the record violates the primary key I assume about 3 gig seems to be the magic number, less than that is lightning fast, more than that is extreemly slow -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show Index Into Cursor?
Is there a way to force the results of Show Index From table Into a table? TIA Dan
RE: Show Index Into Cursor?
You will have to put the value in a resultset and scroll through the resultset. Natively you can do this with MySQL 4.0.x but you could use a programming language. -Original Message- From: Daniel Cummings To: [EMAIL PROTECTED] Sent: 7/16/04 1:19 PM Subject: Show Index Into Cursor? Is there a way to force the results of Show Index From table Into a table? TIA Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IFNULL returns NULL when it shouldn't
How do I prevent IFNULL and ISNULL from returning a null? I have the following query where this is occurring, Select IFNULL(sum(qty),0) from inventory where partnumber=111 group by partnumber; If the partnumber has never been in inventory then the sum and ifnull functions both return null. If the partnumber has been in inventory then the sum returns the correct sum but I need it to return a zero if it's null. It also doesn't matter if I use the ISNULL funtion or the Is Null operation with a IF statement. Any suggestions?
Re: IFNULL returns NULL when it shouldn't
Hi Ed, How do I prevent IFNULL and ISNULL from returning a null? I have the following query where this is occurring, Select IFNULL(sum(qty),0) from inventory where partnumber=111 group by partnumber; If the partnumber has never been in inventory then the sum and ifnull functions both return null. If the partnumber has been in inventory then the sum returns the correct sum but I need it to return a zero if it's null. It also doesn't matter if I use the ISNULL funtion or the Is Null operation with a IF statement. It doesn't return NULL, it returns an empty set. However, if you remove the GROUP BY, it will return 0 just fine. Why are you doing the grouping anyway? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Show Index Into Cursor?
Victor, Natively you can't do this? With SqlPassthrough into FoxPro it returns a cursor but I was hoping to store it into a temporary table on the Backend (MySql) because the access code is so much cleaner. Dan -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Friday, July 16, 2004 11:35 AM To: 'Daniel Cummings '; '[EMAIL PROTECTED] ' Subject: RE: Show Index Into Cursor? You will have to put the value in a resultset and scroll through the resultset. Natively you can do this with MySQL 4.0.x but you could use a programming language. -Original Message- From: Daniel Cummings To: [EMAIL PROTECTED] Sent: 7/16/04 1:19 PM Subject: Show Index Into Cursor? Is there a way to force the results of Show Index From table Into a table? TIA Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Show Index Into Cursor?
You could write your own user defined function similar to show index if you wanted to achieve this natively. -Original Message- From: Daniel Cummings To: 'Victor Pendleton' Cc: [EMAIL PROTECTED] Sent: 7/16/04 2:25 PM Subject: RE: Show Index Into Cursor? Victor, Natively you can't do this? With SqlPassthrough into FoxPro it returns a cursor but I was hoping to store it into a temporary table on the Backend (MySql) because the access code is so much cleaner. Dan -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Friday, July 16, 2004 11:35 AM To: 'Daniel Cummings '; '[EMAIL PROTECTED] ' Subject: RE: Show Index Into Cursor? You will have to put the value in a resultset and scroll through the resultset. Natively you can do this with MySQL 4.0.x but you could use a programming language. -Original Message- From: Daniel Cummings To: [EMAIL PROTECTED] Sent: 7/16/04 1:19 PM Subject: Show Index Into Cursor? Is there a way to force the results of Show Index From table Into a table? TIA Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql growing pains, 4 days to create index on one table!
matt the issue is insert speed, I get 150k-1M records daily, of these, only 5-10 % are new records, I load the EBCDIC file into a temp table, and then do insert ignore into historytable select * from temp table Since you have a temp table created (no keys I assume), use the command mysqldump -v -e -n -t dbname tablename filename.sql This should create insert statements with many values in a single insert. Then use the client program to insert them to you db. mysql -u matt -p dbname filename.sql This is very fast way to insert rows. Speeds up insertion by at least 10x on my large tables (11 million rows). I noticed someone with 1000x more rows w/o problems. BTW, by forcing the table to have packed keys, the docs say it will slow your insertion. Maybe not that much, i don't know. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mutiple MSQL installation on G5- will performance increase ?
If your not using the cpu fully, you definitely need to optimize mysqld's settings and/or optimize your queries. Even if the cpu is running full out, optimizing queries to examine fewer rows can help. Yes Ware, the CPU is not fully utilized. It shows just 10 -15% utilization. Could you provide some direction on how to optimize the mysqld's settings? will using my_huge.cnf will do or do we need to change any parameters? Thanks Kareen __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! 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]
Re: Mutiple MSQL installation on G5- will performance increase ?
Kart v wrote: If your not using the cpu fully, you definitely need to optimize mysqld's settings and/or optimize your queries. Even if the cpu is running full out, optimizing queries to examine fewer rows can help. Yes Ware, the CPU is not fully utilized. It shows just 10 -15% utilization. Could you provide some direction on how to optimize the mysqld's settings? will using my_huge.cnf will do or do we need to change any parameters? This is a topic that could take pages of explanation: http://www.oreilly.com/catalog/hpmysql/ Are you running only on mysql on the box? That's a big issue. If so start with my-huge.cnf and tweak based on your usage patterns and the mysql manual. At a start, give mysqld a lot of memory. Next, look at your queries using explain statements. Nothing will be fast until yo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mutiple MSQL installation on G5- will performance increase ?
Kart v wrote: If your not using the cpu fully, you definitely need to optimize mysqld's settings and/or optimize your queries. Even if the cpu is running full out, optimizing queries to examine fewer rows can help. Yes Ware, the CPU is not fully utilized. It shows just 10 -15% utilization. Could you provide some direction on how to optimize the mysqld's settings? will using my_huge.cnf will do or do we need to change any parameters? [sorry, hit send too early] This is a topic that could take pages of explanation: http://www.oreilly.com/catalog/hpmysql/ Are you running only on mysql on the box? That's a big issue. If so start with my-huge.cnf and tweak based on your usage patterns and the mysql manual. At a start, give mysqld a lot of memory. http://dev.mysql.com/doc/mysql/en/Server_system_variables.html Next, look at your queries using explain statements. You'll want to write your queries/add indices/create summary tables until you get the explains looking good. This too is a complex area. The mysql manual has a lot of info to start with: http://dev.mysql.com/doc/mysql/en/EXPLAIN.html Nothing will be fast until you utilize the cpu. We've found mysql runs very well on the G5. Our G5 XServe is about 2-4X faster than a DP 1.42 G4 Tower, depending on query. Some of that is the chip, some the bus, some the extra memory the G5 can take. It is of course specific to our queries and setup. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deleting duplicates from table
Hello, I'm trying to solve a task in Mysql 4: I have a table rec_id | record_ref | keyword ** 1| 2| apple 2| 2|orange 3| 2|apple 4| 3|mango ** How can I delete records where pair (record_ref and keyword) are identical? In this case either record #1 or #3, but not both. Thank you, Lana _ Add photos to your messages with MSN Premium. Get 2 months FREE* http://join.msn.com/?pgmarket=en-capage=byoa/premxAPID=1994DI=1034SU=http://hotmail.com/encaHL=Market_MSNIS_Taglines -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deleting duplicates from table
Hello, I'm trying to solve a task in Mysql 4: I have a table rec_id | record_ref | keyword ** 1| 2| apple 2| 2|orange 3| 2|apple 4| 3|mango ** How can I delete records where pair (record_ref and keyword) are identical? In this case either record #1 or #3, but not both. Thank you, Lana _ Free yourself from those irritating pop-up ads with MSn Premium. Get 2months FREE* http://join.msn.com/?pgmarket=en-capage=byoa/premxAPID=1994DI=1034SU=http://hotmail.com/encaHL=Market_MSNIS_Taglines -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Consistency among multiple connections
Hi everybody, I have the following scenario. Several computers with shared disk in a LAN. Each of these computer has a MySQL server that serves several databases. I have several clients that communicate with a Java process that I have in each computer to answer queries for a given database (it connects to the MySQL server). I also have a Java process that updates continuously the databases; this process executes only in the local computer: it takes advantage from the fact that the disk is shared in order to update all the databases as quick as possible when it is needed. I am observing some problems: 1) It seems to be a consistency problem. Updates that are performed by the process that updates the databases are not always seen by the clients. 2) Sometimes, it seems that some tables get corrupted, as clients get errors relative to the table handler. Do I need to do something special to ensure the consistency? Does this scenario make sense to you? I know that I could use different processes in each computer to perform the updates (not taking advantage of the shared disk) but then I would have to synchronize these processes to perform the updates at the same time (the updates are the result of a simulation, and they must be performed at specific relative times). Thanks in advance for any suggestion, Sergio P.S. I also get sometimes an error: General error, message from server: Can't lock file (errno: 116). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Get Updated Rows
Is there a way of getting the number of updated rows without using the API calls? TIA Dan
Re: deleting duplicates from table
I think one way to solve it would be to move your data to a new copy of your table. Assuming record_ref and keyword are separate fields... CREATE TABLE newtable SELECT * FROM oldtable GROUP BY CONCAT( record_ref, keyword ); Wes On Jul 16, 2004, at 7:08 PM, L a n a wrote: Hello, I'm trying to solve a task in Mysql 4: I have a table rec_id | record_ref | keyword ** 1| 2| apple 2| 2|orange 3| 2|apple 4| 3|mango ** How can I delete records where pair (record_ref and keyword) are identical? In this case either record #1 or #3, but not both. Thank you, Lana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE_DB and DROP_DB protocol messages, missing error
In article [EMAIL PROTECTED], [EMAIL PROTECTED] (Ton Hospel) writes: The previous mail is about version 4.0.20 by the way. Another thing I notice is that in COM_FIELD_LIST the parsing for the wildcard seem iffy. The code does: if (!(thd-query=fields=thd-memdup(packet,thd-query_length+1))) break; If I leave out the wildcard arg completely, this seems to pick up random memory, since it doesn't check if this point extends beyond the packet boundary. If I pass a string like ab, the thd-query_length still returns 0, so it copies one char (the 'a') and the rest is again random memory (199 times \xa5 actually in my tests with full debug) So for the moment I only support passing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Get Updated Rows
What are you using to perform the deletes? -Original Message- From: Daniel Cummings To: [EMAIL PROTECTED] Sent: 7/16/04 7:10 PM Subject: Get Updated Rows Is there a way of getting the number of updated rows without using the API calls? TIA Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
