innodb filesystem on software raid
I bought a supermicro 6013p-t for the 4 sata raid hard drives support. Unfortunately, it doesn't really have raid at all. So I'm forced to use software raid. What I'd like to use is fedora core 2 with an innodb filesystem on a software raid partition according to these instructions: http://dev.mysql.com/doc/mysql/en/InnoDB_Raw_Devices.html Has anybody done this? I'm not a linux expert and so I'm not sure exactly how to set this up. When I try to setup software raid in linux, I'm forced to pick a filesystem and its mount point as part of the process of creating a software raid partition. So this is the part that's stumping me. How do I create a software raid raw device only to use as an innodb filesystem? Is this possible? Or maybe this can't be done without hardware raid and I need to buy a new server? GNU's Parted software (http://www.gnu.org/software/parted/) has this to say regarding features, Supported disk labels: raw access (useful for RAID and LVM), ms-dos, etc. But I've been unable to create a raw software raid device with that as well. Scott Mueller AccelerateBiz Managed Hosting http://www.acceleratebiz.com http://www.acceleratebiz.com/ Phone: (800) 360-7360 Fax: (270) 778-3081
MySQL/PHP Tunneling
Hello, Recently lot of MySQL clients try to overcome host based privilege system of MySQL by using PHP tunneling method. In this method they call up a PHP file in the server and the PHP file executes a query and sends the data in XML format. I am using C API() and I was just wondering if somebody is working on such tunnels i.e. a PHP file and its corresponding C/++ code that will fill up MYSQL_RES structures correctly so that I can use them to C API() without any problem. Otherwise, i guess i have to write one for myself. Regards, Karam __ 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]
SV: MySQL/InnoDB crashes system
Hi I still got this freeze problem, i have found out that this bug is related to InnoDB, i converted the table that gives problems back to MyISAM, and the dump operation runs fine just as it did before, but as soon as i convert it to InnoDB and dump this table my system freezes but not the first time, usually i can do a dump 2-10 times before it goes wrong. I am certain that it is not a heat problem, or any other hardware problem it could be a conflict between a driver and MySQL. I got 2 identical servers running in a replication setup, i do the testing on my backup server but i can create the freeze on both servers, we run on MySQL 4.0.18, but i have also tried 4.0.20a and 4.1.3-beta. the servers config: Intel Pentium 4 2.53 Ghz QDI Superb 4E-A 533 motherboard 1gb DDR333 ram 2x Seagate CHEETAH 73.5 gb U160 SCSI in Raid 1 Adaptec 2110S Raid controller Windows 2000 Server UK 3Com 996B Gigabit NIC The servers have SP4 and all updates if anyone have an idea on how to troubleshoot this? Thanks Nickolai Nielsen -Oprindelig meddelelse- Fra: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sendt: 9. juli 2004 05:07 Til: [EMAIL PROTECTED] Emne: Re: MySQL/InnoDB crashes system Nickolai, this very much sounds like a hardware fault. No MySQL or InnoDB bug should be able to freeze the WHOLE operating system. And SELECT ... INTO OUTFILE ... is a very basic operation in the database. Regards, Heikki - Original Message - From: Nickolai Nielsen [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, July 08, 2004 8:33 PM Subject: MySQL/InnoDB crashes system hi this SQL frezes the system: SELECT * FROM journal into OUTFILE 'c:/Backup/current/journal.asc' FIELDS terminated by '|' LINES terminated by '\r\n' this started after the table was converted to InnoDB, usualy it runs normaly the first time, but on 2-5 run it frezes the system so i have to reboot the server. System: Windows 2000 Server MySQL 4.0.18 commandline: mysqld --console --log --log-warnings --debug=d,info,error,query,general,whe re:O,/mysqld_3.trace this is a trace output: do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamid into OUTFILE 'c:/30Min-Backup/current_stress/ptanamid.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 3 n_length: 19 int_length: 0 open_table: info: inserting table 02A5B348 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 258048 data_file: 126648 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamid type: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 mi_lock_database: info: lock_type: 0 mi_lock_database: info: old lock: 2 mi_lock_database: info: changed: 0 w_locks: 0 do_select: info: 5277 records output dispatch_command: info: query ready close_thread_tables: info: thd-open_tables=02A5B348 do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamnese into OUTFILE 'c:/30Min-Backup/current_stress/ptanamnese.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 4 n_length: 53 int_length: 0 open_table: info: inserting table 02A5AC40 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 9187328 data_file: 9766188 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamnesetype: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 *** here it crashed *** Thanks Nickolai Nielsen -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SV: MySQL/InnoDB crashes system
Hi Again i forgot to mention that this table has 527101 rows, and takes 90mb when it is dumped to the disk. I dont have this freeze problem when i dump the smaller smaller tables that also uses InnoDB. i Also discovered that it is not on the same posistion ind the dump file the lockup happens. Nickolai -Oprindelig meddelelse- Fra: Nickolai Nielsen [mailto:[EMAIL PROTECTED] Sendt: 10. juli 2004 16:28 Til: [EMAIL PROTECTED] Emne: SV: MySQL/InnoDB crashes system Hi I still got this freeze problem, i have found out that this bug is related to InnoDB, i converted the table that gives problems back to MyISAM, and the dump operation runs fine just as it did before, but as soon as i convert it to InnoDB and dump this table my system freezes but not the first time, usually i can do a dump 2-10 times before it goes wrong. I am certain that it is not a heat problem, or any other hardware problem it could be a conflict between a driver and MySQL. I got 2 identical servers running in a replication setup, i do the testing on my backup server but i can create the freeze on both servers, we run on MySQL 4.0.18, but i have also tried 4.0.20a and 4.1.3-beta. the servers config: Intel Pentium 4 2.53 Ghz QDI Superb 4E-A 533 motherboard 1gb DDR333 ram 2x Seagate CHEETAH 73.5 gb U160 SCSI in Raid 1 Adaptec 2110S Raid controller Windows 2000 Server UK 3Com 996B Gigabit NIC The servers have SP4 and all updates if anyone have an idea on how to troubleshoot this? Thanks Nickolai Nielsen -Oprindelig meddelelse- Fra: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sendt: 9. juli 2004 05:07 Til: [EMAIL PROTECTED] Emne: Re: MySQL/InnoDB crashes system Nickolai, this very much sounds like a hardware fault. No MySQL or InnoDB bug should be able to freeze the WHOLE operating system. And SELECT ... INTO OUTFILE ... is a very basic operation in the database. Regards, Heikki - Original Message - From: Nickolai Nielsen [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, July 08, 2004 8:33 PM Subject: MySQL/InnoDB crashes system hi this SQL frezes the system: SELECT * FROM journal into OUTFILE 'c:/Backup/current/journal.asc' FIELDS terminated by '|' LINES terminated by '\r\n' this started after the table was converted to InnoDB, usualy it runs normaly the first time, but on 2-5 run it frezes the system so i have to reboot the server. System: Windows 2000 Server MySQL 4.0.18 commandline: mysqld --console --log --log-warnings --debug=d,info,error,query,general,whe re:O,/mysqld_3.trace this is a trace output: do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamid into OUTFILE 'c:/30Min-Backup/current_stress/ptanamid.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 3 n_length: 19 int_length: 0 open_table: info: inserting table 02A5B348 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 258048 data_file: 126648 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamid type: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 mi_lock_database: info: lock_type: 0 mi_lock_database: info: old lock: 2 mi_lock_database: info: changed: 0 w_locks: 0 do_select: info: 5277 records output dispatch_command: info: query ready close_thread_tables: info: thd-open_tables=02A5B348 do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamnese into OUTFILE 'c:/30Min-Backup/current_stress/ptanamnese.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 4 n_length: 53 int_length: 0 open_table: info: inserting table 02A5AC40 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 9187328 data_file: 9766188 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamnesetype: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 *** here it crashed *** Thanks Nickolai Nielsen -- 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] -- 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]
Finding the records in one table that are not in another table
I have 2 tables - ProductsOLD and ProductsNEW. I need to find the records that are in the ProductsOLD table and are NOT in ProductsNEW (this will tell me which products have been discontinued). Here's some sample data: +==+ | ProductsOLD | +==+ + Vendor | ID | ++-+ | AAD| 1 | | AAD| 2 | | AAD| 3 |* | BBD| 1 |* | BBD| 2 | | BBD| 3 |* ++-+ (* = these are the products that are NOT in the ProductsNEW table) +==+ | ProductsNEW | +==+ + Vendor | ID | ++-+ | AAD| 1 | | AAD| 2 | | AAD| 5 | | BBD| 2 | | BBD| 7 | | BBD| 10 | ++-+ I need to know the query that would result in: +==+ + Vendor | ID | ++-+ | AAD| 3 | | BBD| 1 | | BBD| 3 | ++-+ There is no primary key for either ProductsNEW or ProductsOLD. The data comes from our distributor, and they don't have any field which would be unique from record to record. If a primary key is essential, I can pre-process the tables to create one. Thanks in advance for your help!!! -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding the records in one table that are not in another table
Jeff Gannaway wrote: I have 2 tables - ProductsOLD and ProductsNEW. I need to find the records that are in the ProductsOLD table and are NOT in ProductsNEW (this will tell me which products have been discontinued). Here's some sample data: +==+ | ProductsOLD | +==+ + Vendor | ID | ++-+ | AAD| 1 | | AAD| 2 | | AAD| 3 |* | BBD| 1 |* | BBD| 2 | | BBD| 3 |* ++-+ (* = these are the products that are NOT in the ProductsNEW table) +==+ | ProductsNEW | +==+ + Vendor | ID | ++-+ | AAD| 1 | | AAD| 2 | | AAD| 5 | | BBD| 2 | | BBD| 7 | | BBD| 10 | ++-+ snip Hi Jeff What you are looking for is a LEFT JOIN - it would look something like this: SELECT a.* FROM ProductsOLD a LEFT JOIN ProductsNEW b ON a.Vendor = b.Vendor AND a.ID = b.ID WHERE b.ID IS NULL HTH Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding the records in one table that are not in another table
Jeff, You need to use a Left Join which will join rows from the second table even if the rows from the second table does not exist, and returns NULL for all column for the second table.. You then use the Where clause to check for a particular Table2.field that should exist (like Id column) and if it doesn't then the row in table 2 does not exist. Example: select po.id from ProductsOld po left join ProductsNew pn on po.id=pn.id where pn.id is null This will list only po.id where it does not exist in pn. If you have more than a hundred rows, there should be some sort on index on 'id' in both tables so the join can use an index. Mike http://dev.mysql.com/doc/mysql/en/JOIN.html At 11:03 AM 7/10/2004, you wrote: I have 2 tables - ProductsOLD and ProductsNEW. I need to find the records that are in the ProductsOLD table and are NOT in ProductsNEW (this will tell me which products have been discontinued). Here's some sample data: +==+ | ProductsOLD | +==+ + Vendor | ID | ++-+ | AAD| 1 | | AAD| 2 | | AAD| 3 |* | BBD| 1 |* | BBD| 2 | | BBD| 3 |* ++-+ (* = these are the products that are NOT in the ProductsNEW table) +==+ | ProductsNEW | +==+ + Vendor | ID | ++-+ | AAD| 1 | | AAD| 2 | | AAD| 5 | | BBD| 2 | | BBD| 7 | | BBD| 10 | ++-+ I need to know the query that would result in: +==+ + Vendor | ID | ++-+ | AAD| 3 | | BBD| 1 | | BBD| 3 | ++-+ There is no primary key for either ProductsNEW or ProductsOLD. The data comes from our distributor, and they don't have any field which would be unique from record to record. If a primary key is essential, I can pre-process the tables to create one. Thanks in advance for your help!!! -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- 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 Speed this Query Up? (Solved)
Using INNER JOINs with the STRAIGHT_JOIN option so that the main table displayed first in the EXPLAIN eliminated the temporary table being created and the query speed went from 2.3 seconds to 0.2 seconds. Note that this only works for queries where the offset in the LIMIT is small. As the offset approaches the total row count, the speed gradually decreases, so much so that the query eventually goes from 2.3 seconds to 4.6 seconds. However since the first few pages (newest data)are accessed most of the time, this is not a problem. _ FREE pop-up blocking with the new MSN Toolbar get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Between Operator
Craig- are you sure you want to test ranking_id '10' ??? -martin - Original Message - From: Craig Hoffman [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Friday, July 09, 2004 3:51 PM Subject: Re: Between Operator Thanks everyone for helping out.I took Michael's advice and made a new table called ranking and two columns. It defiantly cleared some things up but I am still having issues using the BETWEEN operator. I just need to pull up everything BETWEEEN 10 and 18 and it keeps adding additional rows. Suggestions? What am I doing wrong? Here is my query: SELECT area, style, route, stars, date_climbed, ranking.* FROM routes, ranking WHERE ranking.id = ranking.rating BETWEEN ranking.id '10' AND ranking.id = '18' AND routes.rating = ranking.rating AND area = 'Eldorado Canyon' AND style = 'Traditonal' GROUP BY route ORDER BY id DESC Thanks, Craig On Jul 9, 2004, at 1:17 PM, Pete Harlan wrote: On Fri, Jul 09, 2004 at 09:39:02AM -0500, Craig Hoffman wrote: Style: Traditional Area: Yosemite Rating: From: 5.5 To: 5.10c ... SELECT * FROM routes, users WHERE area='$area' AND style='$style' BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route ORDER BY rating ASC ; For some reason which I am not seeing, this query is not doing what it should be doing. Does anyone have any suggestions? For starters your between syntax isn't correct (but is parsable in ways you didn't want). You probably want: select * from routes, users where area = '$area' and style = '$style' and rating between '$rating1' and '$rating2' group by route order by rating As others have pointed out, your ratings aren't something MySQL will know how to order. That's a separate problem (and more difficult to solve), but the between syntax is also one. --Pete -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL stops immediately after starting
I am trying to run the MySQL that comes with Red Hat Linux 7.3. It is MySQL 3.23.49 for Red Hat Linux on i386. I can not get it to start and then stay on. From root I type : /etc/init.d/mysqld start I get : Starting MySQL [OK] then it immediately goes back to root: [EMAIL PROTECTED] root] # When I look in: /var/lib/mysql/localhost.localdomain.err I see: /usr/libexec/mysqld : unrecognized option'--key_buffer=16M I changed 'key_buffer' to 'key_buffer_size', but then the err was 'unrecognized option'--key_buffer_size' I have tried to reinstal mysql and mysql server from the CD with the -U command, but this did not make a difference. Also, I have run the commands :mysql_install_db cd /usr /usr/bin/safe_mysqld From this I get : [2] 272 [EMAIL PROTECTED] usr]# starting mysqld daemon with databases from /var/lib/mysql 040708 17:13:59 mysqld ended It then does not go to a # prompt, but seems to hang. If I type anything and then return it returns : ERROR 2002 : Can't connect to local MySQLserver through socket '/tmp/mysql.sock (2) [2-] - Done I have consulted the manual as well as tried a lot of things, but no help. I am new to this so please forgive it the answer is obvious. -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Document Upload Facility
Hello again. I'm new to MySQL and already very impressed with it's flexibility, speed and functionality. This in mind, I am looking for a way to allow users to upload documents to the server for later retrieval by an administrator. Can this be done or will I have to find a nasty third party tool...? 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.
Is there an easy way to find duplicate records in a table?
I have a table that our distributor sent us. The table doesn't have any keys. It does, however, have 7,782 duplicate records. I found this out when I tried to have MySQL make a unique product ID by combining 2 fields of each record. Here's what I need to know... Is there a MySQL command that will reveal duplicate entries in a table, and find them by looking at just 2 fields??? I'm trying to create a primary key in the new table by combining 2 fields together (Vendor and ID). Sample data format: +==+ | ImportTable | +==+ | Vendor | ID | Price | ++-+---+ | AAD| 1 | $9.98 | | AAD| 1 | $3.98 | | AAD| 52 | $9.98 | | BCD| 2 | $8.98 | | BCD| 5 | $8.98 | | CSX| 44 | $7.98 | ++-+---+ I'd like something that will identify 'AAD 1' as a duplicate, even though their Prices are different. Thanks!! -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Document Upload Facility
Michael Mason wrote: I'm new to MySQL and already very impressed with it's flexibility, speed and functionality. This in mind, I am looking for a way to allow users to upload documents to the server for later retrieval by an administrator. Can this be done or will I have to find a nasty third party tool...? You should be able to do this, but be aware that storing arbitrary binary data might have security implications. Basically, what you end up doing is having a table something like this: create table user_files(user_name varchar(255) not null, user_file blob); Then you use your html code on a web page to let users upload a file. You take that file and insert it into the database. I've done it before and it works ok. Just make sure to check that if someone uploads an executable file you don't accidentally execute at some point. I don't have code handy to share, but if you need some I could probably knock something out quickly. bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Which records are not contained in 2 different tables?
I've got one huge table (table a), and two smaller tables (tables b and c) I need to find which records in 'table a' are not in 'table b' nor are they in 'table c'. The Primary Key for all 3 tables is 'ProductID'. I looked at the LEFT JOIN command in the docs, but it looks like you can only compare 1 table to 1 table. How do I do this? -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/PHP Tunneling
Karam, A much more secure way is to use OpenSSH (www.openssh.org) to create an encrypted connection between the local machine and the MySQL server and to use the -L option to forward MySQL commands from the localhost to the MySQL server. For example, ssh -l Login account URL or IP Address of MySQL server -L 3306:URL or IP Address of MySQL server:3306 Once the ssh connection has been made you tell your client program that the MySQL server is on localhost and ssh will encrypt and forward the commands to the actual server. Regards, Eldon At 09:53 am 7/10/2004, you wrote: Hello, Recently lot of MySQL clients try to overcome host based privilege system of MySQL by using PHP tunneling method. In this method they call up a PHP file in the server and the PHP file executes a query and sends the data in XML format. I am using C API() and I was just wondering if somebody is working on such tunnels i.e. a PHP file and its corresponding C/++ code that will fill up MYSQL_RES structures correctly so that I can use them to C API() without any problem. Otherwise, i guess i have to write one for myself. Regards, Karam __ 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is there an easy way to find duplicate records in a table?
select Vendor, ID, count(*) from ImportTable group by Vendor, ID having count(*) 1; -Original Message- From: Jeff Gannaway [mailto:[EMAIL PROTECTED] Sent: Saturday, July 10, 2004 5:11 PM To: [EMAIL PROTECTED] Subject: Is there an easy way to find duplicate records in a table? I have a table that our distributor sent us. The table doesn't have any keys. It does, however, have 7,782 duplicate records. I found this out when I tried to have MySQL make a unique product ID by combining 2 fields of each record. Here's what I need to know... Is there a MySQL command that will reveal duplicate entries in a table, and find them by looking at just 2 fields??? I'm trying to create a primary key in the new table by combining 2 fields together (Vendor and ID). Sample data format: +==+ | ImportTable | +==+ | Vendor | ID | Price | ++-+---+ | AAD| 1 | $9.98 | | AAD| 1 | $3.98 | | AAD| 52 | $9.98 | | BCD| 2 | $8.98 | | BCD| 5 | $8.98 | | CSX| 44 | $7.98 | ++-+---+ I'd like something that will identify 'AAD 1' as a duplicate, even though their Prices are different. Thanks!! -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- 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]
Need help with my LEFT JOIN query...
I'm trying to SELECT a field (ProductID) from 'Table a' WHERE two corresponding fields are equal (a.PUBLISHER = b.Vendor AND a.NUMBER = b.VIN) Table 'a' (approximately 100,000 records): ++ | ImportLiebermansStep3Add | ++ | ProductID | PUBLISHER | NUMBER | +---+---++ | ACC_4076 | ACC | 4076 | | BCD_300 | BCD | 300| | DEC_R50 | DEC | R50| | WIN_220 | WIN | 220| +---+---++ Table 'b' (approximately 20,000 records): ++ | ProductsOLD| ++ | ProductID | Vendor| VIN| +---+---++ | | ACC | 4076 | | | BCD | | | | DEC | R50| +---+---++ Here's my Query SELECT a.ProductID FROM ImportLiebermansStep3Add a LEFT JOIN ProductsOLD b ON a.PUBLISHER=b.Vendor AND a.NUMBER=b.VIN WHERE b.ProductID IS NULL; But this query runs, and runs, and runs, and runs, and runs without ever giving results. What am I missing??? Thanks! -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql persistent connection...
i'm trying to get a better understanding of how to implement persistent connections with mysql in either perl/php basically i'm trying to solve the issue of whether a web app has to essentially perform a new mysql_connect on every page that's going to be doing any database access. using mysql_connect apparently creates a resource id that gets destroyed when the web page is no longer being used... can the implementation of persistent connections resolve this issue, and how does the resourceID get passed from page to page.. i'm not able to get the Session vars to contain the resourceID... thanks... -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb filesystem on software raid
I highly recommend simply using ext3 for your Linux setup. The 1 or 2 percent performance benefit that you may get from raw partitions is way outweighed by complexness of backups of the raw data. either way: First I would suggest you read the Linux RAID howto: http://www.tldp.org/HOWTO/Software-RAID-HOWTO.html Here are the basic steps: create a /etc/raidtab file for your array (probably md0) using a 32k or 64k chunk size (hint: man raidtab) run mkraid to initialize the new raid array (md0) (hint: man mkraid) if you want to use raw partitions: - #this is redhat/fedora specific add /dev/md0 to /etc/sysconfig/rawdevices (hint: man raw) add chown mysql:mysql /dev/raw/raw0 to /etc/init.d/rc.local if you want to use ext3: - mke2fs -j -T largefile4 /dev/md0 (hint: man mke2fs) --- Scott Mueller [EMAIL PROTECTED] wrote: I bought a supermicro 6013p-t for the 4 sata raid hard drives support. Unfortunately, it doesn't really have raid at all. So I'm forced to use software raid. What I'd like to use is fedora core 2 with an innodb filesystem on a software raid partition according to these instructions: http://dev.mysql.com/doc/mysql/en/InnoDB_Raw_Devices.html Has anybody done this? I'm not a linux expert and so I'm not sure exactly how to set this up. When I try to setup software raid in linux, I'm forced to pick a filesystem and its mount point as part of the process of creating a software raid partition. So this is the part that's stumping me. How do I create a software raid raw device only to use as an innodb filesystem? Is this possible? Or maybe this can't be done without hardware raid and I need to buy a new server? GNU's Parted software (http://www.gnu.org/software/parted/) has this to say regarding features, Supported disk labels: raw access (useful for RAID and LVM), ms-dos, etc. But I've been unable to create a raw software raid device with that as well. Scott Mueller AccelerateBiz Managed Hosting http://www.acceleratebiz.com http://www.acceleratebiz.com/ Phone: (800) 360-7360 Fax: (270) 778-3081 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Weeding out duplicates
At 10:25 -0600 on 07/09/2004, Jonathan Duncan wrote about Re: Weeding out duplicates: Lachlan, I want to identify the entries in the table where the email addresses are the same as another entry. Whatever else is in the record does not matter to me. However, a second requirement for the query is that it show me the last duplicate instead of the first. This way I keep the first entries and remove subsequent ones. Thanks, Jonathan Duncan If you are willing to go with a PHP/MySQL solution as opposed to a pure MySQL one, try this: 1) Use that query to get a list of what Email Addresses are duplicated. 2) Now do a query with a WHERE Email=Duplicate-Email-Address and fetch the Primary Key. 3) Do a Delete by Primary Key for all rows fetched in 2 EXCEPT for those keys fetched in 1. The sequence is that you do step 1, loop though the results one at a time (step 2) doing step 3 in that loop. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Weeding out duplicates
At 17:25 -0600 on 07/09/2004, Jonathan Duncan wrote about Re: Weeding out duplicates: For the information of someone who may need it in the future. I used Jeffrey's idea for determining duplicates. Then I created a temporary table, and used insert...select to put the id's of the duplicates in the temporary table. Then it was a simple delete from table where temp.id=table.id. Thanks for the help. Jonathan Duncan I responded to your query earlier with a PHP/MySQL solution that was equivalent to this. I only saw your reply after sending my suggestion. Sorry for giving you an answer you had already discovered. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
