Re: InnoDB: Assertion failure
Hi, Michael Fernández M. wrote: 2 CPU Pentium III 700 Mhz Aprox. 4 GB RAM. Redhat 7.2 Mysql version: 4.0.14-standard-log Kernel: Kernel 2.4.18-17.7 (highmem) It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3666809 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. As you are using a 32bit System and Kernel, you are limited to between 2 and 2.7GB per process. If you hit that limit, you run into errors. This is a specific 32bit limitation and is not solved by the high mem kernel. You have to decrease mysql memory usage. regards Nils (I have a dejá-vu here, did you post that question before?) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: incompatible libmysqld.a
Carlos Soria wrote: Thank you for your answers. gcc (GCC) 4.1.1 20060724 (prerelease) (4.1.1-3mdk) mysql Ver 14.12 Distrib 5.0.24a, for mandriva-linux-gnu (i586) using readline 5.1 GNU C Library development release version 2.4, I think MySql 5.0 requires glibc 2.3. I do not know if v2.4 is a problem. It compiles with glibc v2.5 on Fedora Core 6 This computer is a laptop Acer Aspire 5633WLMi, Intel Core Duo processor. Can you file a bug report at bugs.mysql.com? thanks -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Ekiga/Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using contraints on Mysql
Hi, I'm looking for using FIELD CONSTRAINTS on Mysql but I can´t find it ... Doesn´t Mysql implement it?? For example: age int 0 MySQL doesn't support declarative CHECK CONSTRAINTs, you could use triggers as a workaround. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problems with a select
First at all, i`m spanish so please, apologize for my english Hello to everybody, i`m a newby with mysql and i have a problem with a select I`m making a database for a hostel I have 2 tables for reservations, one is called rooms and have 2 fields (id_room, tipo)and the other one is ocupation with 3 colums(id_reservation, date and id_room) I`m using innodb database I insert a register on the ocupation table for each day of the reservations, with the id_room and the id_reservation I wanna mean, if u make a reservation for the 22-3-2007 to 24-3-2007 i will do that rooms table id_reservationdateid_room 00012007-03-22101 00012007-03-23101 00012007-03-24101 I know must there are a lot of better ways to do it, but i think is the simplest one Now, my problem with the query is when i need to know for a especific date, witch rooms are avaliable I wanna mean, if i try to check for the 22-03-2007 the query must return me all the rooms except the 101 Using SQL i can do it with a minus sentence, but the mysql version i`m using(5.0.24a) cant support it Have anyone any idea? Thanks, and please, forgive my english ;)
Re: problems with a select
I'm spanish speaker too .. but I write it in english for everyone can read it. The version 5 implements subqueries. You can do something like: SELECT id_room FROM rooms WHERE id_room NOT IN (SELECT id_room FROM reservations WHERE date = '22-03-07'); Regards, RAUL DUQUE Bogotá, Colombia - Original Message - From: Yo Mismo [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 26, 2007 8:41 AM Subject: problems with a select First at all, i`m spanish so please, apologize for my english Hello to everybody, i`m a newby with mysql and i have a problem with a select I`m making a database for a hostel I have 2 tables for reservations, one is called rooms and have 2 fields (id_room, tipo)and the other one is ocupation with 3 colums(id_reservation, date and id_room) I`m using innodb database I insert a register on the ocupation table for each day of the reservations, with the id_room and the id_reservation I wanna mean, if u make a reservation for the 22-3-2007 to 24-3-2007 i will do that rooms table id_reservationdateid_room 00012007-03-22101 00012007-03-23101 00012007-03-24101 I know must there are a lot of better ways to do it, but i think is the simplest one Now, my problem with the query is when i need to know for a especific date, witch rooms are avaliable I wanna mean, if i try to check for the 22-03-2007 the query must return me all the rooms except the 101 Using SQL i can do it with a minus sentence, but the mysql version i`m using(5.0.24a) cant support it Have anyone any idea? Thanks, and please, forgive my english ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
restaure a dump.sql file encoded in utf8 to a mysql server with mysql client set to latin1?
Hello, Is it possible to restaure a .sql file encoded in utf8 to mysql with mysql client set to latin1? I've got a forum on a server which has got it's client set to utf8 I need to move the forum to a new server. But this new server has got postfix and proftp that seem to need the mysql client set to latin1 I can only dump the database using phpmyadmin and phpmyadmin puts errors in the sql file if I ask it to transform the file to latin1 as utf8 contains some characters that latin1 does not. So my dump file has to be in utf8. But with mysql client set to latin1 I get copyright symbols instead of accents. And it is impossible to see the posts on the forum or connect to the forum. My dump file is about 90Mb so it's not possible to restore it with phpmyadmin. So I use this command : mysql -u root -p dump.sql If the mysql client is set to utf8 all characters are copied correctly but proftp and postfix stop working. If mysql clien is set to latin1 all characters with accents are bady coppied and the forum is unusable, but proftp and postfix work. So is there a way to restore the sql dump file to mysql with the mysql client set to latin1 Any ideas? Thanks ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
return all dates between two dates
I need to create an inline view that will give me all dates between two specified. Here is the question posed to me that I need to answer: This basic query gets me the last 6 hours of history table entries for an item, grouped into minutes (if an item has a delay of 5 seconds, it takes the max out of each 11 entries per minutes). set @iid=231; set @hid=1; select from_unixtime(t.clock), DATE_FORMAT(from_unixtime(t.clock),'%j %h:%i %p') as mins, MAX(t.value) as value_max from history t where t.itemid = (select itemid from items where itemid = @iid and hostid = @hid) and DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 6 HOUR) = from_unixtime(t.clock) group by mins order by t.clock; It'll fetch 360 entries (360 minutes in 6 hours) that looks like this: from_unixtime(t.clock) mins value_max 2/24/2007 05:56:12055 05:56 AM0.29 2/24/2007 05:57:02055 05:57 AM0.2 2/24/2007 05:58:02055 05:58 AM0.23 2/24/2007 06:01:03055 06:01 AM0.02 2/24/2007 06:02:02055 06:02 AM0.08 The format you see for mins is DAY_OF_YEAR HOUR:MIN AM/PM. DAY_OF_YEAR is unique, as in 55th day of this year. Okay, so if I can JOIN this table with another table/view/sub query that has EVERY MINUTE of the last 6 hours in a similarly formatted column like 'mins', we should be able to spot the missing 2 minutes. from_unixtime(t.clock)mins value_max 2/24/2007 05:56:12055 05:56 AM0.29 2/24/2007 05:57:02055 05:57 AM0.2 2/24/2007 05:58:02055 05:58 AM0.23 2/24/2007 05:59:02055 05:59 AMNULL -- from the join 2/24/2007 06:00:02055 06:00 AMNULL - from the join 2/24/2007 06:01:03055 06:01 AM0.02 2/24/2007 06:02:02055 06:02 AM0.08 Bryan Cantwell Director, Development (M) 469.361.1272 [EMAIL PROTECTED] www.FireScope.net
Re: backup stratergy
Juan, InnoDB Hot Backup is non-free. A 1-year license costs 390 euros + VAT, and a perpetual license 990 euros + VAT. http://www.innodb.com/order.php The Perl script innobackup can be used to make consistent backups of MyISAM tables also, but those backup require the locking of MyISAM tables, and are not in that sense 'hot' or 'online'. InnoDB Hot Backup works with all MySQL versions up to 5.1. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL ... Hi Juan, Thanks a lot for the quick reply. Any idea how much it would cost for ibbackup for innodb. Will mysql be providing this with any of their new release. regards anandkl On 2/23/07, Juan Eduardo Moreno [EMAIL PROTECTED] wrote: Ananda, For Innodb the best is Innodb Hot Backup ( www.innodb.com (US$) ) For MyISAM you can use a simple backup ( copy/paste) of your files. Also, you can do snapshots using mysqldump. Also, you can use Zmanda ( www.zmanda.com). Regards, Juan Eduardo On 2/23/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, Can you please direct me to any good documentation for a good backup and recovery stratergy for MyISAM and INNODB in mysql. regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what's better query to update table
hi to all! I have a table that contains ratings of suppleirs. almost 2000 records. administrator uses a form with radio buttons (values R, A and NR) to chanage rating for each supplier. after he submit I have an array where index is $supp_id and value is $new_rating. Since, admin changes the rating 2-3 times a month, it's not big deal, but I would like to know what is correct solution: have a three queries, e.g.: mysql_query( update suppliers set rating='R' where supp_id=1 or supp_id=5 or supp_id=8 or supp_id=23 or ... or supp_id=1786); mysql_query( update suppliers set rating='A' where supp_id=2 or supp_id=3 or supp_id=9 or supp_id=18 or ... or supp_id=1823); mysql_query( update suppliers set rating='NR' where supp_id=4 or supp_id=6 or supp_id=7 or ... or supp_id=1824); or have a query for each record: mysql_query(update suppliers set rating='R' where supp_id=1); mysql_query(update suppliers set rating='A' where supp_id=2); mysql_query(update suppliers set rating='A' where supp_id=3); mysql_query(update suppliers set rating='NR' where supp_id=4); e.t.c. Or, there is other solution? Thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup stratergy
Whats wrong with using the --single-transaction switch for backing up InnoDB tables? What does the Hot Backup product do that this doesn't? Thanks, -Ryan Juan Eduardo Moreno wrote: Ananda, For Innodb the best is Innodb Hot Backup ( www.innodb.com (US$) ) For MyISAM you can use a simple backup ( copy/paste) of your files. Also, you can do snapshots using mysqldump. Also, you can use Zmanda ( www.zmanda.com). Regards, Juan Eduardo On 2/23/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, Can you please direct me to any good documentation for a good backup and recovery stratergy for MyISAM and INNODB in mysql. regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: return all dates between two dates
Bryan A subqueryless (ie join) version of your query will likely be faster: select from_unixtime(t.clock), DATE_FORMAT(from_unixtime(t.clock),'%j %h:%i %p') as mins, MAX(t.value) as value_max from history t join items i using (itemid) where t.itemid = @iid and i.hostid = @hid and DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 6 HOUR) = from_unixtime(t.clock) group by mins order by t.clock; For your second question, it might be easier to build a calendar table with one row for each possible relevant datetime value, and left join that calendar table to your history table. PB Bryan Cantwell wrote: I need to create an inline view that will give me all dates between two specified. Here is the question posed to me that I need to answer: This basic query gets me the last 6 hours of history table entries for an item, grouped into minutes (if an item has a delay of 5 seconds, it takes the max out of each 11 entries per minutes). set @iid=231; set @hid=1; select from_unixtime(t.clock), *DATE_FORMAT(from_unixtime(t.clock),'%j %h:%i %p') as mins*, MAX(t.value) as value_max from history t where t.itemid = (select itemid from items where itemid = @iid and hostid = @hid) and DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 6 HOUR) = from_unixtime(t.clock) *group by mins* order by t.clock; It'll fetch 360 entries (360 minutes in 6 hours) that looks like this: *from_unixtime(t.clock) mins value_max* 2/24/2007 05:56:12055 05:56 AM0.29 2/24/2007 05:57:02055 05:57 AM0.2 2/24/2007 05:58:02055 05:58 AM0.23 2/24/2007 06:01:03055 06:01 AM0.02 2/24/2007 06:02:02055 06:02 AM0.08 The format you see for mins is DAY_OF_YEAR HOUR:MIN AM/PM. DAY_OF_YEAR is unique, as in 55^th day of this year. Okay, so if I can JOIN this table with another table/view/sub query that has EVERY MINUTE of the last 6 hours in a similarly formatted column like 'mins', we should be able to spot the missing 2 minutes. from_unixtime(t.clock)mins value_max 2/24/2007 05:56:12055 05:56 AM0.29 2/24/2007 05:57:02055 05:57 AM0.2 2/24/2007 05:58:02055 05:58 AM0.23 2/24/2007 05:59:02055 05:59 AMNULL ß from the join 2/24/2007 06:00:02055 06:00 AMNULL - from the join 2/24/2007 06:01:03055 06:01 AM0.02 2/24/2007 06:02:02055 06:02 AM0.08 Bryan Cantwell Director, Development (M) 469.361.1272 [EMAIL PROTECTED] FireScope.net www.FireScope.net No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.412 / Virus Database: 268.18.4/702 - Release Date: 2/25/2007 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.412 / Virus Database: 268.18.4/702 - Release Date: 2/25/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load sharing
I have a potential client that is anticipating rapid growth of a web site they want me to build. Some quick research tells me that there is the potential for as many as 50 million users that will access the site for an hour or two every day. All of those users will be located in the USA so most of the access will be during the day.. To use the web site you will have to have an account and log in. At this time I can't really say how much data will need to be stored about each user. If this site grows as much as this client thinks, will I need to have some kind of load sharing system to access the database? I was reading in the MySQL manual about the NDB Cluster storage engine. Is this something that would work well in a situation like this? One thing that was mentioned was the possibility of having servers in different locations which seems to make the Cluster storage engine not a good choice. Can someone here give some insight and suggest other options I could look into? -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load sharing
On 2/26/07, Chris W [EMAIL PROTECTED] wrote: Can someone here give some insight and suggest other options I could look into? you could replicate all the data between web/application servers, and use the local store as a read-only database, and push all your updates to a central update 'master' server. You could also play with multiple-master replication. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.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: what's better query to update table
I would use three queries, as in your first example, but use IN followed by
a list of IDs that you construct. That would be easier to build than all of
those OR conditions. Loop through the input something like this (not tried,
and probably a little sloppy):
$rating_list = array(); // Build three lists in this array, one for each
rating (A, R, NR)
for ($i = 0; $i count($supp_id); $i++) {
$rating_list[$new_rating] .= ' . $supp_id . ',;
}
// Each list probably has one extra comma
$thislen = strlen($rating_list[A]);
if ($thislen 0) {
$rating_list[A] = substr($rating_list[A], 0, $thislen - 1);
}
$query = update suppliers set rating='A' where supp_id IN
(${rating_list[A]});
... And so forth.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, February 26, 2007 12:38 PM
To: mysql@lists.mysql.com
Subject: what's better query to update table
hi to all!
I have a table that contains ratings of suppleirs. almost
2000 records.
administrator uses a form with radio buttons (values R, A and NR) to
chanage rating for each supplier.
after he submit I have an array where index is $supp_id and value is
$new_rating.
Since, admin changes the rating 2-3 times a month, it's not
big deal, but
I would like to know what is correct solution: have a three queries,
e.g.:
mysql_query(
update suppliers
set rating='R'
where supp_id=1 or supp_id=5 or supp_id=8 or supp_id=23 or ... or
supp_id=1786);
mysql_query(
update suppliers
set rating='A'
where supp_id=2 or supp_id=3 or supp_id=9 or supp_id=18 or ... or
supp_id=1823);
mysql_query(
update suppliers
set rating='NR'
where supp_id=4 or supp_id=6 or supp_id=7 or ... or
supp_id=1824);
or
have a query for each record:
mysql_query(update suppliers set rating='R' where supp_id=1);
mysql_query(update suppliers set rating='A' where supp_id=2);
mysql_query(update suppliers set rating='A' where supp_id=3);
mysql_query(update suppliers set rating='NR' where supp_id=4);
e.t.c.
Or, there is other solution?
Thanks for any help.
-afan
--
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: load sharing
Complementing Chris : http://www.onlamp.com/lpt/a/6549Asume that your Application Server is Tomcat. BALANCING SERVERS: You can implement some servers or appliance only for balancing your primary connections. This servers or appliance you must implement using Hearbeat ( only). For example one of the balancing servers fail. APPLICATION SEVER Your x ( quantity) of applications servers receive the connections from Balancing Servers and distribute across the diferent nodes of MySQL using LVS. You must implement Hearbeat for each application server too ( just in case of failure of some app. server). DATABASE SERVERS MySQL and multimaster replication, please see below http://www.onlamp.com/lpt/a/6549 Technology : for OS try to use Linux, for Hearbeat try to use UltraMonkey ( www.ultramonkey.org) for Balancing try to use LVS ( Linux). I hope this help. Regards. Juan Eduardo On 2/26/07, Chris McKeever [EMAIL PROTECTED] wrote: On 2/26/07, Chris W [EMAIL PROTECTED] wrote: Can someone here give some insight and suggest other options I could look into? you could replicate all the data between web/application servers, and use the local store as a read-only database, and push all your updates to a central update 'master' server. You could also play with multiple-master replication. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.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: load sharing
If you can wait for 5.1 (in beta now), you can use partitioning to store a client on a different database in a different geographical site. You'd need to partition by region/state (assuming you capture address info). If you wanted to do any reporting, however, you'd need to set up a data warehouse, and every night do an extract-transform-load (ETL) from the regional sites into your main database. It might make more sense to have mini-sites all over the country - database, web and application servers. Since it sounds like development hasn't started, you can probably go with 5.1 - it should be released before summer. David Chris W wrote: I have a potential client that is anticipating rapid growth of a web site they want me to build. Some quick research tells me that there is the potential for as many as 50 million users that will access the site for an hour or two every day. All of those users will be located in the USA so most of the access will be during the day.. To use the web site you will have to have an account and log in. At this time I can't really say how much data will need to be stored about each user. If this site grows as much as this client thinks, will I need to have some kind of load sharing system to access the database? I was reading in the MySQL manual about the NDB Cluster storage engine. Is this something that would work well in a situation like this? One thing that was mentioned was the possibility of having servers in different locations which seems to make the Cluster storage engine not a good choice. Can someone here give some insight and suggest other options I could look into? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disk parition full
Hi I have looked up earlier postings on disk partitions and mysql and couldn't find any discussion threads. Mysql is storing data in the /usr partition of Redhat 9. The /usr partition is about 50% full. Within a year it will outlast the available space on /usr. The /var partition on the same machine has lots of free space. I am thinking if it is possible to store the data in the /var paritition, that would be a temporary fix until I can move the whole operation on to a bigger disk which could take a year (given the company bottom line :). Can someone please tell me how to manage multiple disk partitions in mysql? BTW, the mysql is version 4 or something like that. Thanks Murthy - Don't be flakey. Get Yahoo! Mail for Mobile and always stay connected to friends.
Re: Really worried about DELETE statement - THANKS
At 10:42 PM 2/25/2007, mos wrote: At 06:13 PM 2/25/2007, Miles Thompson wrote: Would someone please check this delete query? This should delete all rows from the geodesic_user_data that have no match in the subscriber table, but another set of eyes would be appreciated. DELETE geodesic_user_data FROM geodesic_user_data LEFT JOIN subscriber ON geodesic_classifieds_userdata.id=subscriber.GeoClassID WHERE subscriber.GeoClassID IS NULL; Our hosting company uses MySQL 4.0.17 so the subquery approach is not possible. Thanks in advance - Miles Miles, It looks fine but you could have checked it yourself using: select * FROM geodesic_user_data LEFT JOIN subscriber ON geodesic_classifieds_userdata.id=subscriber.GeoClassID WHERE subscriber.GeoClassID IS NULL; -- Thanks Mos, and to everyone else who responded. I had tested it with a SELECT; it was just that other set of eyes that I needed for confirmation. I did what I should have donein the first place - exported the appropriate tables with data, created them on my computer, and tested the deletes. They worked fine. Tomorrow evening they will get run for real. Regards - Miles -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.412 / Virus Database: 268.18.4/702 - Release Date: 2/25/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really worried about DELETE statement - THANKS
At 07:51 PM 2/26/2007, Miles Thompson wrote: At 10:42 PM 2/25/2007, mos wrote: At 06:13 PM 2/25/2007, Miles Thompson wrote: Would someone please check this delete query? This should delete all rows from the geodesic_user_data that have no match in the subscriber table, but another set of eyes would be appreciated. DELETE geodesic_user_data FROM geodesic_user_data LEFT JOIN subscriber ON geodesic_classifieds_userdata.id=subscriber.GeoClassID WHERE subscriber.GeoClassID IS NULL; Our hosting company uses MySQL 4.0.17 so the subquery approach is not possible. Thanks in advance - Miles Miles, It looks fine but you could have checked it yourself using: select * FROM geodesic_user_data LEFT JOIN subscriber ON geodesic_classifieds_userdata.id=subscriber.GeoClassID WHERE subscriber.GeoClassID IS NULL; -- Thanks Mos, and to everyone else who responded. I had tested it with a SELECT; it was just that other set of eyes that I needed for confirmation. I did what I should have donein the first place - exported the appropriate tables with data, created them on my computer, and tested the deletes. They worked fine. Tomorrow evening they will get run for real. Regards - Miles Miles, I always back up the data if I'm not sure what the SQL will do to it. I use: create table bu_MyTable select * from MyTable This builds a backup table without the indexes so I can easily copy the data back using an Insert statement. Good luck tomorrow. :) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
